<?php
  function get_phieu_xuat_nhap($filter) {
  switch ($filter['type']) {
    case 'nhap':
      $query = db_select('dt_san_pham_nhap', 'spn')->extend('PagerDefault')->extend('TableSort');
      $query->fields('spn', array('id', 'ma_phieu_nhap', 'ma_to_san_xuat', 'thoi_gian'));
      if ($filter['ma_ref']) {
        $query->condition('spn.ma_to_san_xuat', $filter['ma_ref']);
      }
      $query->leftJoin('dt_to_san_xuat', 'tsx', 'tsx.ma_to_san_xuat = spn.ma_to_san_xuat');
      $query->addField('tsx', 'ten_to_san_xuat');
      //$query->orderByHeader($header);
      $result = $query->orderBy('spn.thoi_gian', 'DESC')->limit(20)->execute();
      $admin_perm = user_access('admin xoa sp');
      $admin_perm_edit = user_access('admin sua sp');
      $data = array();
      while ($rs = $result->fetchObject()) {
        $output = '<a href="../san_pham/phieu_xuat_nhap/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_nhap . '" onclick="return check_xoa_phieu();">Xóa</a>';
        $data[] = array(
          'ma_phieu_nhap' => $rs->ma_phieu_nhap,
          'ma_to_san_xuat' => $rs->ten_to_san_xuat,
          'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
          'thao_tac' => ($admin_perm ? ($output . ' | ')
            : '') . ($admin_perm_edit
            ? l('Sửa ', 'san_pham/phieu_xuat_nhap/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_nhap) . ' | '
            : '') . l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_nhap)
        );
      }
      break;
    case 'hoi':
      $query = db_select('dt_san_pham_hoi', 'sph')->extend('PagerDefault')->extend('TableSort');
      $query->fields('sph', array('id', 'ma_phieu_hang_hoi', 'ma_khach_hang', 'thoi_gian', 'trang_thai', 'trang_thai_bao_hanh'));
      if ($filter['ma_ref']) {
        $query->condition('sph.ma_khach_hang', $filter['ma_ref']);
      }
      $query->leftJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = sph.ma_khach_hang');
      $query->addField('kh', 'ten_khach_hang');
      $result = $query->orderBy('sph.thoi_gian', 'DESC')->limit(20)->execute();
      $admin_perm = user_access('admin xoa sp');
      $admin_perm_edit = user_access('admin sua sp');
      $data = array();
      while ($rs = $result->fetchObject()) {
        $output = '<a href="../../san_pham/phieu_xuat_nhap/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_hang_hoi . '" onclick="return check_xoa_phieu();">Xóa</a>';
        $data[] = array(
          'ma_phieu_hang_hoi' => $rs->ma_phieu_hang_hoi,
          'ma_khach_hang' => $rs->ten_khach_hang,
          'trang_thai' => $rs->trang_thai_bao_hanh ? 'Đã trả' : 'Chưa trả',
          'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
          'thao_tac' => ($admin_perm ? ($output . ' | ')
            : '') . ($admin_perm_edit
            ? l('Sửa ', 'san_pham/phieu_xuat_nhap/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_hang_hoi) . ' | '
            : '') . l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_hang_hoi)
        );
      }
      break;
    case 'nguyen':
      $query = db_select('dt_san_pham_nguyen', 'spng')->extend('PagerDefault')->extend('TableSort');
      $query->fields('spng', array('id', 'ma_phieu_hang_nguyen', 'ma_khach_hang', 'thoi_gian', 'trang_thai', 'tong_tien'));
      if ($filter['ma_ref']) {
        $query->condition('spng.ma_khach_hang', $filter['ma_ref']);
      }
      $query->leftJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = spng.ma_khach_hang');
      $query->addField('kh', 'ten_khach_hang');
      // $query->orderByHeader($header);
      $result = $query->orderBy('spng.thoi_gian', 'DESC')->limit(20)->execute();
      $khop_phieu = user_access('khop phieu san pham');
      $quyen = user_access('xem gia san pham');
      $admin_perm = user_access('admin xoa sp');
      $admin_perm_edit = user_access('admin sua sp');
      $nhap_gia = (in_array($filter['type'], array('nguyen')) && user_access('nhap gia san pham'));
      $data = array();
      while ($rs = $result->fetchObject()) {
        $output = '<a href="../../san_pham/phieu_xuat_nhap/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_hang_nguyen . '" onclick="return check_xoa_phieu();">Xóa</a>';
        if ($quyen) {
          $data[] = array(
            'ma_phieu_hang_nguyen' => $rs->ma_phieu_hang_nguyen,
            'ma_khach_hang' => $rs->ten_khach_hang,
            'tong_tien' => number_format($rs->tong_tien),
            'trang_thai' => $rs->tong_tien ? 'Đã nhập giá tiền' : 'Chưa nhập giá tiền',
            'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
            'thao_tac' => ($admin_perm ? ($output . ' | ')
              : '') . ($admin_perm_edit
              ? l('Sửa ', 'san_pham/phieu_xuat_nhap/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_hang_nguyen) . ' | '
              : '') . l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_hang_nguyen) .
                          (($nhap_gia && $rs->tong_tien == 0 && $rs->trang_thai)
                            ? ' | ' . l('Nhập giá', 'san_pham/phieu_xuat_nhap/nhap_gia/' . $filter['type'] . '/' . $rs->ma_phieu_hang_nguyen)
                            : '')
          );
        }
        else {
          $data[] = array(
            'ma_phieu_hang_nguyen' => $rs->ma_phieu_hang_nguyen,
            'ma_khach_hang' => $rs->ten_khach_hang,
            'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
            'thao_tac' => ($admin_perm ? ($output . ' | ')
              : '') . ($admin_perm_edit
              ? l('Sửa ', 'san_pham/phieu_xuat_nhap/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_hang_nguyen) . ' | '
              : '') . l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_hang_nguyen) .
                          (($nhap_gia && $rs->tong_tien == 0 && $rs->trang_thai)
                            ? ' | ' . l('Nhập giá', 'san_pham/phieu_xuat_nhap/nhap_gia/' . $filter['type'] . '/' . $rs->ma_phieu_hang_nguyen)
                            : '')
          );
        }
      }
      break;
    case 'hong':
      $query = db_select('dt_xuat_san_pham_hong', 'spn')->extend('PagerDefault')->extend('TableSort');
      $query->fields('spn', array('id', 'ma_phieu_xuat', 'ma_to_san_xuat', 'thoi_gian'));
      if ($filter['ma_ref']) {
        $query->condition('spn.ma_to_san_xuat', $filter['ma_ref']);
      }
      $query->leftJoin('dt_to_san_xuat', 'tsx', 'tsx.ma_to_san_xuat = spn.ma_to_san_xuat');
      $query->addField('tsx', 'ten_to_san_xuat');
      // $query->orderByHeader($header);
      $result = $query->orderBy('spn.thoi_gian', 'DESC')->limit(20)->execute();
      $admin_perm = user_access('admin xoa sp');
      $admin_perm_edit = user_access('admin sua sp');
      $data = array();
      while ($rs = $result->fetchObject()) {
        $output = '<a href="../../san_pham/phieu_xuat_nhap/xoa_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_xuat . '" onclick="return check_xoa_phieu();">Xóa</a>';
        $data[] = array(
          'ma_phieu_xuat' => $rs->ma_phieu_xuat,
          'ma_to_san_xuat' => $rs->ten_to_san_xuat,
          'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
          'thao_tac' => ($admin_perm ? ($output . ' | ')
            : '') . ($admin_perm_edit
            ? l('Sửa ', 'san_pham/phieu_xuat_nhap/sua_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_xuat) . ' | '
            : '') . l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/' . $filter['type'] . '/' . $rs->ma_phieu_xuat)
        );
      }
      break;
  }
  return $data;
}

  function insert_loai_sanpham($uid, $ten_loai_san_pham, $ghi_chu) {
    $thoi_gian = time();
    $query = db_insert('dt_loai_san_pham');
    $arr = array(
      'uid' => $uid,
      'loai_san_pham' => $ten_loai_san_pham,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $query->execute();
    return $ten_loai_san_pham;
  }

  function insert_sanpham($uid, $ten_san_pham, $don_vi, $loai, $gia_tien, $so_luong, $so_luong_hong, $ghi_chu) {
    $thoi_gian = time();
    $trang_thai = 1;
    $query = db_insert('dt_san_pham');
    $arr = array(
      'uid' => $uid,
      'ma_thanh_pham' => '',
      'ten_thanh_pham' => $ten_san_pham,
      'don_vi' => $don_vi,
      'idloai' => $loai,
      'gia_tien' => $gia_tien,
      'ghi_chu' => $ghi_chu,
      'so_luong' => $so_luong,
      'so_luong_hong' => $so_luong_hong,
      'trang_thai' => $trang_thai,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_san_pham = $id;
    while (strlen($ma_san_pham) < 3) {
      $ma_san_pham = '0' . $ma_san_pham;
    }
    $ma_san_pham = 'SP_' . $ma_san_pham;
    db_update('dt_san_pham')
      ->fields(array(
                    'ma_thanh_pham' => $ma_san_pham,
               ))
      ->condition('id', $id)
      ->execute();
    $query_n = db_insert('dt_san_pham_du_lieu');
    $arr_n = array(
      'uid' => $uid,
      'ma_thanh_pham' => $ma_san_pham,
      'so_luong' => $so_luong,
      'so_luong_hong' => $so_luong_hong,
      'thoi_gian' => $thoi_gian
    );
    $query_n->fields(array_keys($arr_n));
    $query_n->values($arr_n);
    $query_n->execute();
    if ($loai == 2) {
      $query_vt = db_insert('dt_vat_tu');
      $arr_vt = array(
        'uid' => $uid,
        'trang_thai' => $trang_thai,
        'ma_vat_tu' => $ma_san_pham,
        'ten_vat_tu' => $ten_san_pham,
        'don_vi' => $don_vi,
        'so_luong' => $so_luong,
        'so_luong_hong' => $so_luong_hong,
        'gia_tien' => $gia_tien,
        'ghi_chu' => $ghi_chu,
        'thoi_gian' => $thoi_gian
      );
      $query_vt->fields(array_keys($arr_vt));
      $query_vt->values($arr_vt);
      $query_vt->execute();
      $query_vtn = db_insert('dt_vat_tu_du_lieu');
      $arr_vtn = array(
        'uid' => $uid,
        'ma_vat_tu' => $ma_san_pham,
        'so_luong' => $so_luong,
        'so_luong_hong' => $so_luong_hong,
        'thoi_gian' => $thoi_gian
      );
      $query_vtn->fields(array_keys($arr_vtn));
      $query_vtn->values($arr_vtn);
      $query_vtn->execute();
    }
  }

  function update_loai_sanpham($uid, $ten_loai_san_pham, $ghi_chu, $id) {
    //    $thoi_gian = time();
    db_update('dt_loai_san_pham')
      ->fields(array(
                    //                 'uid' => $uid,
                    'loai_san_pham' => $ten_loai_san_pham,
                    'ghi_chu' => $ghi_chu,
                    //                 'thoi_gian' => $thoi_gian,
               ))
      ->condition('id', $id)
      ->execute();
  }

  function update_sanpham($uid, $ma_san_pham, $ten_san_pham, $don_vi, $loai, $gia_tien, $ghi_chu) {
    $thoi_gian = time();
    db_update('dt_san_pham')
      ->fields(array(
                    'uid' => $uid,
                    'ten_thanh_pham' => $ten_san_pham,
                    'don_vi' => $don_vi,
                    'idloai' => $loai,
                    'gia_tien' => $gia_tien,
                    'thoi_gian' => $thoi_gian,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_thanh_pham', $ma_san_pham)
      ->execute();
    db_update('dt_vat_tu')
      ->fields(array(
                    'uid' => $uid,
                    'ten_vat_tu' => $ten_san_pham,
                    'don_vi' => $don_vi,
                    //'gia_tien' => $gia_tien,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_vat_tu', $ma_san_pham)
      ->execute();
  }

  function update_sanpham_admin($uid, $ma_san_pham, $ten_san_pham, $don_vi, $loai, $gia_tien, $ghi_chu, $so_luong, $so_luong_hong) {
    $thoi_gian = time();
    db_update('dt_san_pham')
      ->fields(array(
                    'uid' => $uid,
                    'ten_thanh_pham' => $ten_san_pham,
                    'don_vi' => $don_vi,
                    'idloai' => $loai,
                    'so_luong' => $so_luong,
                    'so_luong_hong' => $so_luong_hong,
                    'gia_tien' => $gia_tien,
                    'thoi_gian' => $thoi_gian,
                    'ghi_chu' => $ghi_chu,
               ))
      ->condition('ma_thanh_pham', $ma_san_pham)
      ->execute();
    db_update('dt_vat_tu')
      ->fields(array(
                    'uid' => $uid,
                    'ten_vat_tu' => $ten_san_pham,
                    'don_vi' => $don_vi,
                    'so_luong' => $so_luong,
                    'so_luong_hong' => $so_luong_hong,
                    //'gia_tien' => $gia_tien,
                    'ghi_chu' => $ghi_chu,
                    'thoi_gian' => $thoi_gian
               ))
      ->condition('ma_vat_tu', $ma_san_pham)
      ->execute();
  }

  function get_loai_san_pham($header) {
    $query = db_select('dt_loai_san_pham', 'lsp')->extend('PagerDefault')->extend('TableSort');
    $query->fields('lsp', array('id', 'loai_san_pham', 'ghi_chu', 'thoi_gian'));
    $query->orderByHeader($header);
    $result = $query->limit(20)->execute();
    $data = array();
    $i = 0;
    $edit_perm = user_access('sua loai san pham');
    while ($rs = $result->fetchObject()) {
      $i++;
      $data[] = array(
        'stt' => $i,
        'loai_san_pham' => $rs->loai_san_pham,
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => ($edit_perm ? l('Sửa', 'san_pham/loai_san_pham/sua_loai_san_pham/' . $rs->id) : '')
      );
    }
    return $data;
  }

  function get_loai_san_pham_by_id($id) {
    $query = db_select('dt_loai_san_pham', 'lsp');
    $query->fields('lsp', array('id', 'loai_san_pham', 'ghi_chu'));
    $query->condition('lsp.id', $id);
    return $query->execute()->fetchObject();
  }

  function load_select() {
    $list_loai = array();
    $query = db_select('dt_loai_san_pham', 'lsp');
    $query->fields('lsp', array('id', 'loai_san_pham'));
    $result = $query->execute();
    while ($row = $result->fetchObject())
    {
      $list_loai[$row->id] = $row->loai_san_pham;
    }
    return $list_loai;
  }

  function get_san_pham_autocomplete($string) {
    $query = db_select('dt_san_pham', 'sp')->extend('PagerDefault');
    $query->fields('sp', array('ma_thanh_pham', 'ten_thanh_pham'));
    $query->condition('sp.hien_thi', 0);
    $query->condition(
      db_or()->
        condition('sp.ten_thanh_pham', '%' . db_like($string) . '%', 'LIKE')->
        condition('sp.ma_thanh_pham', $string, '=')->
        condition('sp.ma_thanh_pham', 'SP_' . $string, '=')
    );
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[$rs->ten_thanh_pham . ' [' . $rs->ma_thanh_pham . ']'] = $rs->ten_thanh_pham . ' [' . $rs->ma_thanh_pham . ']';
    }
    return $data;
  }

  function insert_thanh_pham($uid, $ma_to_san_xuat, $ghi_chu, $thoi_gian) {
    $query = db_insert('dt_san_pham_nhap');
    $arr = array(
      'uid' => $uid,
      'ma_to_san_xuat' => $ma_to_san_xuat,
      'ma_phieu_nhap' => '',
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_nhap = $id;
    while (strlen($ma_phieu_nhap) < 3) {
      $ma_phieu_nhap = '0' . $ma_phieu_nhap;
    }
    $ma_phieu_nhap = 'PNTP_' . $ma_phieu_nhap;
    db_update('dt_san_pham_nhap')
      ->fields(array(
                    'ma_phieu_nhap' => $ma_phieu_nhap,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_nhap;
  }

  function insert_thanh_pham_sua($uid, $ma_to_san_xuat, $ghi_chu, $thoi_gian, $ma_phieu) {
    $query = db_insert('dt_san_pham_nhap');
    $arr = array(
      'uid' => $uid,
      'ma_to_san_xuat' => $ma_to_san_xuat,
      'ma_phieu_nhap' => $ma_phieu,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    /*$ma_phieu_nhap = $id;
    while (strlen($ma_phieu_nhap) < 3) {
      $ma_phieu_nhap = '0' . $ma_phieu_nhap;
    }
    $ma_phieu_nhap = 'PNTP_' . $ma_phieu_nhap;
    db_update('dt_san_pham_nhap')
      ->fields(array(
                    'ma_phieu_nhap' => $ma_phieu_nhap,
               ))
      ->condition('id', $id)
      ->execute();*/
    return $id;
  }

  function insert_xuat_hong($uid, $ma_to_san_xuat, $ghi_chu, $thoi_gian) {
    $query = db_insert('dt_xuat_san_pham_hong');
    $arr = array(
      'uid' => $uid,
      'ma_to_san_xuat' => $ma_to_san_xuat,
      'ma_phieu_xuat' => '',
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_nhap = $id;
    while (strlen($ma_phieu_nhap) < 3) {
      $ma_phieu_nhap = '0' . $ma_phieu_nhap;
    }
    $ma_phieu_nhap = 'PXSPH_' . $ma_phieu_nhap;
    db_update('dt_xuat_san_pham_hong')
      ->fields(array(
                    'ma_phieu_xuat' => $ma_phieu_nhap,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_nhap;
  }

  function insert_xuat_hong_sua($uid, $ma_to_san_xuat, $ghi_chu, $thoi_gian, $ma_phieu) {
    $query = db_insert('dt_xuat_san_pham_hong');
    $arr = array(
      'uid' => $uid,
      'ma_to_san_xuat' => $ma_to_san_xuat,
      'ma_phieu_xuat' => $ma_phieu,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    return $id;
  }

  function insert_thanh_pham_chi_tiet($uid, $ma_phieu_nhap, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $query = db_insert('dt_san_pham_nhap_chi_tiet');
    $query->fields(array('uid', 'ma_thanh_pham', 'ma_phieu_nhap', 'so_luong', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_thanh_pham) {
      $arr = array(
        'uid' => $uid,
        'ma_thanh_pham' => $ma_thanh_pham,
        'ma_phieu_nhap' => $ma_phieu_nhap,
        'so_luong' => $so_luong_arr[$key],
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_san_pham('+', $so_luong_arr[$key], $ma_thanh_pham);
    }
    $query->execute();
  }

  function insert_xuat_hong_chi_tiet($uid, $ma_phieu_xuat, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $query = db_insert('dt_xuat_san_pham_hong_chi_tiet');
    $query->fields(array('uid', 'ma_thanh_pham', 'ma_phieu_xuat', 'so_luong', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_thanh_pham) {
      $arr = array(
        'uid' => $uid,
        'ma_thanh_pham' => $ma_thanh_pham,
        'ma_phieu_xuat' => $ma_phieu_xuat,
        'so_luong' => $so_luong_arr[$key],
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      //update_so_luong_san_pham_hong('-', $so_luong_arr[$key], $ma_thanh_pham);
    }
    $query->execute();
  }

  function validate_ma_thanh_pham($ma_thanh_pham_arr, $so_luong_arr, $type, $field_name = 'ten_thanh_pham') {
    $query = db_select('dt_san_pham', 'sp');
    $query->fields('sp', array('ma_thanh_pham', 'so_luong_hong'));
    $query->condition('sp.ma_thanh_pham', $ma_thanh_pham_arr, 'in');
    $result = $query->execute();
    $validate_arr = array();
    $san_pham_arr = array();
    while ($rs = $result->fetchObject()) {
      $validate_arr[] = $rs->ma_thanh_pham;
      $san_pham_arr[$rs->ma_thanh_pham] = $rs;
    }
    $data['validate'] = true;
    foreach ($ma_thanh_pham_arr as $key => $val) {
      if (!in_array($val, $validate_arr)) {
        $data['validate'] = false;
        $data['key'] = $key;
        $data[$field_name] = true;
        $data['message'] = 'Nhập không đúng tên thành phẩm';
        break;
      }
      if ($type == 'xuat hang hong' && $san_pham_arr[$val]->so_luong_hong < $so_luong_arr[$key]) {
        $data['validate'] = false;
        $data['key'] = $key;
        $data['so_luong'] = true;
        $data['message'] = 'Số lượng trong kho không đủ';
        break;
      }
    }
    return $data;
  }

  function insert_san_pham_hang_hoi($uid, $ma_khach_hang, $ghi_chu, $thoi_gian, $trang_thai_bao_hanh = 0) {
    $trang_thai = 1;
    $query = db_insert('dt_san_pham_hoi');
    $arr = array(
      'uid' => $uid,
      'ma_khach_hang' => $ma_khach_hang,
      'ma_phieu_hang_hoi' => '',
      'trang_thai' => $trang_thai,
      'trang_thai_bao_hanh' => $trang_thai_bao_hanh,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_nhap = $id;
    while (strlen($ma_phieu_nhap) < 3) {
      $ma_phieu_nhap = '0' . $ma_phieu_nhap;
    }
    $ma_phieu_nhap = 'PNTPH_' . $ma_phieu_nhap;
    db_update('dt_san_pham_hoi')
      ->fields(array(
                    'ma_phieu_hang_hoi' => $ma_phieu_nhap,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_nhap;
  }

  function insert_san_pham_hang_hoi_sua($uid, $ma_khach_hang, $ghi_chu, $thoi_gian, $ma_phieu) {
    $trang_thai_bao_hanh = 0;
    $trang_thai = 1;
    $query = db_insert('dt_san_pham_hoi');
    $arr = array(
      'uid' => $uid,
      'ma_khach_hang' => $ma_khach_hang,
      'ma_phieu_hang_hoi' => $ma_phieu,
      'trang_thai' => $trang_thai,
      'trang_thai_bao_hanh' => $trang_thai_bao_hanh,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    return $id;
  }

  function insert_san_pham_hang_hoi_chi_tiet($uid, $ma_phieu_nhap, $ma_vat_tu_arr, $so_luong_arr, $so_luong_hoi_arr, $thoi_gian) {
    $query = db_insert('dt_san_pham_hoi_chi_tiet');
    $query->fields(array('uid', 'ma_thanh_pham', 'ma_phieu_hang_hoi', 'so_luong', 'so_luong_hoi', 'don_gia_tru_tien', 'thoi_gian', 'thoi_gian_tra_bh'));
    foreach ($ma_vat_tu_arr as $key => $ma_thanh_pham) {
      $arr = array(
        'uid' => $uid,
        'ma_thanh_pham' => $ma_thanh_pham,
        'ma_phieu_hang_hoi' => $ma_phieu_nhap,
        'so_luong' => $so_luong_arr[$key],
        'so_luong_hoi' => $so_luong_hoi_arr[$key],
        'don_gia_tru_tien' => 0,
        'thoi_gian' => $thoi_gian,
        'thoi_gian_tra_bh' => 0
      );
      $query->values($arr);
      // update_so_luong_san_pham_hong('+', $so_luong_arr[$key], $ma_thanh_pham);
    }
    $query->execute();
  }

  function insert_san_pham_hang_nguyen($uid, $ma_khach_hang, $ghi_chu, $thoi_gian) {
    $trang_thai = 1;
    $tong_tien = 0;
    $query = db_insert('dt_san_pham_nguyen');
    $arr = array(
      'uid' => $uid,
      'ma_khach_hang' => $ma_khach_hang,
      'ma_phieu_hang_nguyen' => '',
      'trang_thai' => $trang_thai,
      'tong_tien' => $tong_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    $ma_phieu_nhap = $id;
    while (strlen($ma_phieu_nhap) < 3) {
      $ma_phieu_nhap = '0' . $ma_phieu_nhap;
    }
    $ma_phieu_nhap = 'PNTPN_' . $ma_phieu_nhap;
    db_update('dt_san_pham_nguyen')
      ->fields(array(
                    'ma_phieu_hang_nguyen' => $ma_phieu_nhap,
               ))
      ->condition('id', $id)
      ->execute();
    return $ma_phieu_nhap;
  }

  function insert_san_pham_hang_nguyen_sua($uid, $ma_khach_hang, $ghi_chu, $thoi_gian, $ma_phieu) {
    $trang_thai = 1;
    $tong_tien = 0;
    $query = db_insert('dt_san_pham_nguyen');
    $arr = array(
      'uid' => $uid,
      'ma_khach_hang' => $ma_khach_hang,
      'ma_phieu_hang_nguyen' => $ma_phieu,
      'trang_thai' => $trang_thai,
      'tong_tien' => $tong_tien,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    return $id;
  }

  function insert_san_pham_hang_nguyen_chi_tiet($uid, $ma_phieu_nhap, $ma_vat_tu_arr, $so_luong_arr, $thoi_gian) {
    $gia_tien = 0;
    $query = db_insert('dt_san_pham_nguyen_chi_tiet');
    $query->fields(array('uid', 'ma_thanh_pham', 'ma_phieu_hang_nguyen', 'so_luong', 'gia_tien', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_thanh_pham) {
      $arr = array(
        'uid' => $uid,
        'ma_thanh_pham' => $ma_thanh_pham,
        'ma_phieu_hang_nguyen' => $ma_phieu_nhap,
        'so_luong' => $so_luong_arr[$key],
        'gia_tien' => $gia_tien,
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_san_pham('+', $so_luong_arr[$key], $ma_thanh_pham);
    }
    $query->execute();
  }

  function insert_san_pham_hang_nguyen_chi_tiet_sua($uid, $ma_phieu_nhap, $ma_vat_tu_arr, $so_luong_arr, $gia_tien_arr, $thoi_gian) {
    $tong_tien = 0;
    $query = db_insert('dt_san_pham_nguyen_chi_tiet');
    $query->fields(array('uid', 'ma_thanh_pham', 'ma_phieu_hang_nguyen', 'so_luong', 'gia_tien', 'thoi_gian'));
    foreach ($ma_vat_tu_arr as $key => $ma_thanh_pham) {
      $arr = array(
        'uid' => $uid,
        'ma_thanh_pham' => $ma_thanh_pham,
        'ma_phieu_hang_nguyen' => $ma_phieu_nhap,
        'so_luong' => $so_luong_arr[$key],
        'gia_tien' => $gia_tien_arr[$key],
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_san_pham('+', $so_luong_arr[$key], $ma_thanh_pham);
      //update công nợ khách hàng
      $tong_tien += $so_luong_arr[$key] * $gia_tien_arr[$key];
    }
    $query->execute();
    db_update('dt_san_pham_nguyen')
      ->fields(array('tong_tien' => $tong_tien))
      ->condition('ma_phieu_hang_nguyen', $ma_phieu_nhap)
      ->execute();
    include_once(drupal_get_path('module', 'khach_hang') . '/model/khach_hang.inc');
    update_tien_no_khach_hang('-', $data['tong_tien'], $phieu->ma_ref);
  }

  function get_san_pham_theo_ma($ma_san_pham) {
    $query = db_select('dt_san_pham', 'sp');
    $query->fields('sp', array('id', 'ma_thanh_pham', 'ten_thanh_pham', 'don_vi', 'idloai', 'gia_tien', 'ghi_chu', 'so_luong', 'so_luong_hong'));
    $query->condition('sp.ma_thanh_pham', $ma_san_pham, '=');
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function get_san_pham($trang_thai, $header = array()) {
    $query = db_select('dt_san_pham', 'sp');
    $query->innerJoin('dt_loai_san_pham', 'lsp', 'sp.idloai=lsp.id');
    $query->fields('sp', array('id', 'ma_thanh_pham', 'ten_thanh_pham', 'don_vi', 'idloai', 'gia_tien', 'thoi_gian'));
    $query->fields('lsp', array('loai_san_pham'));
    $query->condition('sp.trang_thai', $trang_thai);
    //$query->orderByHeader($header);
    $result = $query->execute();
    $data = array();
    $params = $_GET;
    unset($params['q']);
    $str_param = '';
    if (!empty($params)) {
      foreach ($params as $key => $val) {
        $str_param .= '&' . $key . '=' . $val;
      }
    }
    $edit_perm = user_access('sua san pham');
    while ($rs = $result->fetchObject()) {
      if ($trang_thai) {
        $link = '<a href="' . base_path() . 'san_pham/trang_thai/ngung/' . $rs->ma_thanh_pham . '?destination=san_pham' . $str_param . '">Ngừng cung cấp</a>';
      }
      else {
        $link = '<a href="' . base_path() . 'san_pham/trang_thai/tiep_tuc/' . $rs->ma_thanh_pham . '?destination=san_pham/danh_sach_san_pham/ngung_cung_cap' . $str_param . '">Tiếp tục cung cấp</a>';
      }
      $data[] = array(
        'ma_thanh_pham' => $rs->ma_thanh_pham,
        'ten_thanh_pham' => $rs->ten_thanh_pham,
        'idloai' => $rs->loai_san_pham,
        'gia_tien' => number_format($rs->gia_tien),
        'don_vi' => $rs->don_vi,
        'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
        'thao_tac' => ($edit_perm ? l('Sửa', 'san_pham/sua_san_pham/' . $rs->ma_thanh_pham) : '') . ' | ' . $link
      );
    }
    return $data;
  }

  function get_thanh_pham($quyen, $header = array(), $filter) {
    $query = db_select('dt_san_pham', 'sp')->extend('TableSort');
    $query->innerJoin('dt_loai_san_pham', 'lsp', 'sp.idloai=lsp.id');
    $query->fields('sp', array('id', 'ma_thanh_pham', 'ten_thanh_pham', 'don_vi', 'idloai', 'gia_tien', 'so_luong', 'so_luong_hong', 'thoi_gian', 'ghi_chu'));
    $query->fields('lsp', array('loai_san_pham'));
    if ($filter['ma_ref']) {
      $query->condition('sp.ma_thanh_pham', $filter['ma_ref']);
    }
    $query->condition('sp.hien_thi', 0);
    // $query->orderBy('sp.ten_thanh_pham', 'ASC');
    $query->orderByHeader($header);
    $result = $query->execute();
    $data = array();
    $edit_perm = user_access('sua san pham');
    $admin_perm_edit = user_access('admin sua sp');
    $admin_perm = user_access('admin xoa sp');
    $tong_tien = 0;
    $tong_am = 0;
    while ($rs = $result->fetchObject()) {
      $output = '<a href="san_pham/thong_ke_san_pham/xoa_san_pham/' . $rs->ma_thanh_pham . '" onclick="return check_xoa();">Xóa</a>';
      if ($rs->idloai == 1) {
        $tong_am += $rs->so_luong;
      }
      if ($quyen) {
        $thanh_tien = $rs->so_luong * $rs->gia_tien;
        $tong_tien += $thanh_tien;
        $data[] = array(
          'ma_thanh_pham' => $rs->ma_thanh_pham,
          'ten_thanh_pham' => $rs->ten_thanh_pham,
          'idloai' => $rs->loai_san_pham,
          'gia_tien' => number_format($rs->gia_tien),
          'so_luong' => number_format($rs->so_luong),
          'thanh_tien' => number_format($thanh_tien),
          //'so_luong_hong' => number_format($rs->so_luong_hong),
          'don_vi' => $rs->don_vi,
          'thao_tac' => ($admin_perm ? ($output . ' | ')
            : '') . (($edit_perm || $admin_perm_edit)
            ? l('Sửa', 'san_pham/sua_san_pham/' . $rs->ma_thanh_pham)
            : '') . dt_view_ghi_chu('sp_' . $rs->id, 'Ghi chú ' . $rs->ten_thanh_pham, $rs->ghi_chu)
        );
      }
      else {
        $data[] = array(
          'ma_thanh_pham' => $rs->ma_thanh_pham,
          'ten_thanh_pham' => $rs->ten_thanh_pham,
          'idloai' => $rs->loai_san_pham,
          'so_luong' => number_format($rs->so_luong),
          // 'so_luong_hong' => number_format($rs->so_luong_hong),
          'don_vi' => $rs->don_vi,
          'thao_tac' => ($admin_perm ? ($output . ' | ')
            : '') . ($edit_perm ? l('Sửa', 'san_pham/sua_san_pham/' . $rs->ma_thanh_pham)
            : '') . dt_view_ghi_chu('sp_' . $rs->id, 'Ghi chú ' . $rs->ten_thanh_pham, $rs->ghi_chu)
        );
      }
    }
    if ($quyen) {
      $data[] = array(
        array('data' => '', 'colspan' => 4),
        array('data' => 'Tổng ấm', 'class' => array('text-bold')),
        number_format($tong_am),
        array('data' => '', 'colspan' => 3),
      );
      $data[] = array(
        array('data' => '', 'colspan' => 4),
        array('data' => 'Tổng giá trị', 'class' => array('text-bold')),
        number_format($tong_tien) . ' VNĐ',
        array('data' => '', 'colspan' => 3),
      );
    }
    return $data;
  }

  function update_trang_thai_san_pham($trang_thai, $ma_thanh_pham) {
    $thoi_gian = time();
    return db_update('dt_san_pham')
      ->fields(array(
                    'trang_thai' => $trang_thai,
                    'thoi_gian' => $thoi_gian,
               ))
      ->condition('ma_thanh_pham', $ma_thanh_pham)
      ->execute();
  }

  function get_phieu_thanh_pham_theo_ma($type, $ma_phieu) {
    switch ($type) {
      case 'nhap':
        $query = db_select('dt_san_pham_nhap', 'p');
        $query->leftJoin('dt_to_san_xuat', 'tsx', 'tsx.ma_to_san_xuat = p.ma_to_san_xuat');
        $query->fields('p', array('id', 'thoi_gian', 'ghi_chu'));
        $query->addField('p', 'ma_phieu_nhap', 'ma_phieu');
        $query->addField('tsx', 'ten_to_san_xuat', 'ref');
        $query->addField('tsx', 'ma_to_san_xuat', 'ma_ref');
        $query->condition('p.ma_phieu_nhap', $ma_phieu);
        break;
      case 'nguyen':
        $query = db_select('dt_san_pham_nguyen', 'p');
        $query->leftJoin('dt_khach_hang', 'ref', 'ref.ma_khach_hang = p.ma_khach_hang');
        $query->fields('p', array('id', 'ghi_chu', 'thoi_gian', 'trang_thai', 'tong_tien'));
        $query->addField('p', 'ma_phieu_hang_nguyen', 'ma_phieu');
        $query->addField('ref', 'ma_khach_hang', 'ma_ref');
        $query->addField('ref', 'ten_khach_hang', 'ref');
        $query->condition('p.ma_phieu_hang_nguyen', $ma_phieu);
        break;
      case 'hoi':
        $query = db_select('dt_san_pham_hoi', 'p');
        $query->leftJoin('dt_khach_hang', 'ref', 'ref.ma_khach_hang = p.ma_khach_hang');
        $query->fields('p', array('id', 'ghi_chu', 'trang_thai', 'trang_thai_bao_hanh', 'thoi_gian'));
        $query->addField('p', 'ma_phieu_hang_hoi', 'ma_phieu');
        $query->addField('ref', 'ma_khach_hang', 'ma_ref');
        $query->addField('ref', 'ten_khach_hang', 'ref');
        $query->condition('p.ma_phieu_hang_hoi', $ma_phieu);
        break;
      case 'hong':
        $query = db_select('dt_xuat_san_pham_hong', 'p');
        $query->leftJoin('dt_to_san_xuat', 'tsx', 'tsx.ma_to_san_xuat = p.ma_to_san_xuat');
        $query->fields('p', array('id', 'thoi_gian', 'ghi_chu'));
        $query->addField('p', 'ma_phieu_xuat', 'ma_phieu');
        $query->addField('tsx', 'ten_to_san_xuat', 'ref');
        $query->addField('tsx', 'ma_to_san_xuat', 'ma_ref');
        $query->condition('p.ma_phieu_xuat', $ma_phieu);
        break;
      case 'banhang':
        $query = db_select('dt_ban_hang', 'p');
        $query->fields('p', array('id', 'ma_khach_hang', 'ma_phieu_ban_hang', 'trang_thai', 'thuc_thu', 'thoi_gian', 'ghi_chu'));
        $query->condition('p.ma_phieu_ban_hang', $ma_phieu);
        break;
    }
    $result = $query->execute();
    $rs = $result->fetchObject();
    return $rs;
  }

  function get_phieu_thanh_pham_chi_tiet_theo_ma($type, $ma_phieu, $get_table = true) {
    switch ($type) {
      case 'nhap':
        $query = db_select('dt_san_pham_nhap_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian', 'ma_thanh_pham'));
        $query->condition('p.ma_phieu_nhap', $ma_phieu);
        break;
      case 'nguyen':
        $query = db_select('dt_san_pham_nguyen_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian', 'gia_tien', 'ma_thanh_pham'));
        $query->condition('p.ma_phieu_hang_nguyen', $ma_phieu);
        break;
      case 'hoi':
        $query = db_select('dt_san_pham_hoi_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'so_luong_hoi', 'don_gia_tru_tien', 'thoi_gian', 'ma_thanh_pham'));
        $query->condition('p.ma_phieu_hang_hoi', $ma_phieu);
        break;
      case 'hong':
        $query = db_select('dt_xuat_san_pham_hong_chi_tiet', 'p');
        $query->fields('p', array('id', 'so_luong', 'thoi_gian', 'ma_thanh_pham'));
        $query->condition('p.ma_phieu_xuat', $ma_phieu);
        break;
    }
    if ($type != 'hong') {
      $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = p.ma_thanh_pham');
      $query->fields('sp', array('ma_thanh_pham', 'ten_thanh_pham', 'don_vi'));
    }
    $result = $query->execute();
    $data = array();
    if ($get_table) {
      if ($type == 'hoi') {
        $data['header'] = array(
          'Mã thành phẩm',
          'Tên vật tư',
          'Số lượng hồi',
          'Số được bảo hành',
//          'Đơn giá trừ tiền'
        );
        while ($rs = $result->fetchObject()) {
          $data['rows'][] = array(
            array('data' => $rs->ma_thanh_pham, 'class' => array('')),
            array('data' => $rs->ten_thanh_pham, 'class' => array('')),
            array('data' => $rs->so_luong_hoi, 'class' => array('')),
            array('data' => $rs->so_luong, 'class' => array('')),
//            array('data' => ($rs->don_gia_tru_tien ? number_format($rs->don_gia_tru_tien) : 'Lấy hàng'), 'class' => array(''))
          );
        }
      }
      else {
        if ($type == 'nguyen' && user_access('xem gia san pham')) {
          $data['header'] = array(
            'Mã thành phẩm',
            'Tên vật tư',
            'Số lượng',
            'Đơn vị',
            'Đơn giá',
            'Thành tiền'
          );
          while ($rs = $result->fetchObject()) {
            $data['rows'][] = array(
              array('data' => $rs->ma_thanh_pham, 'class' => array('')),
              array('data' => $rs->ten_thanh_pham, 'class' => array('')),
              array('data' => $rs->so_luong, 'class' => array('')),
              array('data' => $rs->don_vi, 'class' => array('')),
              array('data' => number_format($rs->gia_tien), 'class' => array('')),
              array('data' => number_format($rs->gia_tien * $rs->so_luong), 'class' => array('')),
            );
          }
        }
        else {
          if ($type == 'hong') {
            $data['header'] = array(
              'Sản phẩm',
              'Số lượng',
            );
            while ($rs = $result->fetchObject()) {
              $data['rows'][] = array(
                array('data' => $rs->ma_thanh_pham, 'class' => array('')),
                array('data' => $rs->so_luong, 'class' => array('')),
              );
            }
          }
          else {
            $data['header'] = array(
              'Mã thành phẩm',
              'Tên vật tư',
              'Số lượng',
              'Đơn vị',
            );
            while ($rs = $result->fetchObject()) {
              $data['rows'][] = array(
                array('data' => $rs->ma_thanh_pham, 'class' => array('')),
                array('data' => $rs->ten_thanh_pham, 'class' => array('')),
                array('data' => $rs->so_luong, 'class' => array('')),
                array('data' => $rs->don_vi, 'class' => array(''))
              );
            }
          }
        }
      }
    }
    else {
      while ($rs = $result->fetchObject()) {
        $data[] = $rs;
      }
    }
    return $data;
  }

  function get_thong_ke_san_pham_ngay($start_date, $end_date, $start_month, $end_month) {
    $query = db_select('dt_san_pham', 'sp');
    $query->fields('sp', array('id', 'ma_thanh_pham', 'ten_thanh_pham', 'thoi_gian'));
    $query->condition('sp.hien_thi', 0);
    $query->addExpression('(SELECT SUM(n0.so_luong) FROM {dt_san_pham_nhap_chi_tiet} AS n0 WHERE sp.ma_thanh_pham = n0.ma_thanh_pham AND n0.thoi_gian < :n0_start_month)', 'nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(x0.so_luong) FROM {dt_san_pham_nguyen_chi_tiet} AS x0 WHERE sp.ma_thanh_pham = x0.ma_thanh_pham AND x0.thoi_gian < :x0_start_month)', 'nhap_nguyen_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(xk0.xuat_kho) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk0 ,{dt_ban_hang} as bh0 WHERE xk0.ma_phieu_ban_hang=bh0.ma_phieu_ban_hang and sp.ma_thanh_pham = xk0.ma_thanh_pham AND bh0.thoi_gian < :bh0_start_month)', 'xuat_den_thang', array(':bh0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(d.so_luong) FROM {dt_san_pham_du_lieu} AS d WHERE sp.ma_thanh_pham = d.ma_thanh_pham AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(nlbht0.nhan_lai) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht0 WHERE sp.ma_thanh_pham = nlbht0.ma_thanh_pham AND nlbht0.thoi_gian < :nlbht0_start_month)', 'nlbht0_den_thang', array(':nlbht0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(n0.so_luong) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n0 WHERE sp.ma_thanh_pham = n0.ma_vat_tu AND n0.thoi_gian < :n0_start_month)', 'nhap_vt_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(x0.so_luong) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x0 WHERE sp.ma_thanh_pham = x0.ma_vat_tu AND x0.thoi_gian < :x0_start_month)', 'xuat_vt_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(nh0.so_luong) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS nh0 WHERE sp.ma_thanh_pham = nh0.ma_vat_tu AND nh0.thoi_gian < :nh0_start_month)', 'xuat_tkt_vt_den_thang', array(':nh0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(ntk0.so_luong) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk0 WHERE sp.ma_thanh_pham = ntk0.ma_vat_tu AND ntk0.thoi_gian < :ntk0_start_month)', 'nhap_tinh_vt_den_thang', array(':ntk0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(n1.so_luong) FROM {dt_san_pham_nhap_chi_tiet} AS n1 WHERE sp.ma_thanh_pham = n1.ma_thanh_pham AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(x1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS x1 WHERE sp.ma_thanh_pham = x1.ma_thanh_pham AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'hoi_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(h1.so_luong) FROM {dt_san_pham_nguyen_chi_tiet} AS h1 WHERE sp.ma_thanh_pham = h1.ma_thanh_pham AND h1.thoi_gian >= :h1_start_month AND h1.thoi_gian <= :h1_end_month)', 'nguyen_thang', array(':h1_start_month' => $start_month, ':h1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(b1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS b1 ,{dt_san_pham_hoi} AS h1 WHERE b1.ma_phieu_hang_hoi = h1.ma_phieu_hang_hoi and h1.trang_thai_bao_hanh =1 and b1.don_gia_tru_tien = 0 and  sp.ma_thanh_pham = b1.ma_thanh_pham AND b1.thoi_gian_tra_bh <> 0 AND b1.thoi_gian_tra_bh >= :b1_start_month AND b1.thoi_gian_tra_bh <= :b1_end_month)', 'bh_thang', array(':b1_start_month' => $start_month, ':b1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(k1.so_luong) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS k1 WHERE sp.ma_thanh_pham = k1.ma_thanh_pham and k1.tru_tien=0 AND k1.thoi_gian >= :k1_start_month AND k1.thoi_gian <= :k1_end_month)', 'km_thang', array(':k1_start_month' => $start_month, ':k1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(xk1.xuat_kho) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk1 ,{dt_ban_hang} as bh1 WHERE xk1.ma_phieu_ban_hang = bh1.ma_phieu_ban_hang and sp.ma_thanh_pham = xk1.ma_thanh_pham AND bh1.thoi_gian >= :bh1_start_month AND bh1.thoi_gian <= :bh1_end_month)', 'xk_thang', array(':bh1_start_month' => $start_month, ':bh1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(nlbht1.nhan_lai) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht1 WHERE sp.ma_thanh_pham = nlbht1.ma_thanh_pham AND nlbht1.thoi_gian >= :nlbht1_start_month AND nlbht1.thoi_gian <= :nlbht1_end_month)', 'nlbht_thang', array(':nlbht1_start_month' => $start_month, ':nlbht1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(nbht1.so_no) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nbht1 WHERE sp.ma_thanh_pham = nbht1.ma_thanh_pham AND nbht1.thoi_gian >= :nbht1_start_month AND nbht1.thoi_gian <= :nbht1_end_month)', 'nbht_thang', array(':nbht1_start_month' => $start_month, ':nbht1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(n2.so_luong) FROM {dt_san_pham_nhap_chi_tiet} AS n2 WHERE sp.ma_thanh_pham = n2.ma_thanh_pham AND n2.thoi_gian >= :n2_start_date AND n2.thoi_gian <= :n2_end_date)', 'nhap_ngay', array(':n2_start_date' => $start_date, ':n2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(x2.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS x2 WHERE sp.ma_thanh_pham = x2.ma_thanh_pham AND x2.thoi_gian >= :x2_start_date AND x2.thoi_gian <= :x2_end_date)', 'hoi_ngay', array(':x2_start_date' => $start_date, ':x2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(h2.so_luong) FROM {dt_san_pham_nguyen_chi_tiet} AS h2 WHERE sp.ma_thanh_pham = h2.ma_thanh_pham AND h2.thoi_gian >= :h2_start_date AND h2.thoi_gian <= :h2_end_date)', 'nguyen_ngay', array(':h2_start_date' => $start_date, ':h2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(b2.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS b2 ,{dt_san_pham_hoi} AS h2 WHERE b2.ma_phieu_hang_hoi = h2.ma_phieu_hang_hoi and h2.trang_thai_bao_hanh =1 and b2.don_gia_tru_tien = 0 and sp.ma_thanh_pham = b2.ma_thanh_pham AND b2.thoi_gian_tra_bh <> 0 AND b2.thoi_gian_tra_bh >= :b2_start_date AND b2.thoi_gian_tra_bh <= :b2_end_date)', 'bh_ngay', array(':b2_start_date' => $start_date, ':b2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(k2.so_luong) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS k2 WHERE sp.ma_thanh_pham = k2.ma_thanh_pham and k2.tru_tien=0 AND k2.thoi_gian >= :k2_start_date AND k2.thoi_gian <= :k2_end_date)', 'km_ngay', array(':k2_start_date' => $start_date, ':k2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(xk2.xuat_kho) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk2 ,{dt_ban_hang} as bh2 WHERE xk2.ma_phieu_ban_hang=bh2.ma_phieu_ban_hang and sp.ma_thanh_pham = xk2.ma_thanh_pham  AND bh2.thoi_gian >= :bh2_start_date AND bh2.thoi_gian <= :bh2_end_date)', 'xk_ngay', array(':bh2_start_date' => $start_date, ':bh2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(nlbht2.nhan_lai) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht2 WHERE sp.ma_thanh_pham = nlbht2.ma_thanh_pham AND nlbht2.thoi_gian >= :nlbht2_start_date AND nlbht2.thoi_gian <= :nlbht2_end_date)', 'nlbht_ngay', array(':nlbht2_start_date' => $start_date, ':nlbht2_end_date' => $end_date));
    $query->addExpression('(SELECT SUM(nbht2.so_no) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nbht2 WHERE sp.ma_thanh_pham = nbht2.ma_thanh_pham AND nbht2.thoi_gian >= :nbht2_start_date AND nbht2.thoi_gian <= :nbht2_end_date)', 'nbht_ngay', array(':nbht2_start_date' => $start_date, ':nbht2_end_date' => $end_date));
    //    $query->addExpression('(SELECT SUM(n3.so_luong) FROM {dt_san_pham_nhap_chi_tiet} AS n3 WHERE sp.ma_thanh_pham = n3.ma_thanh_pham AND n3.thoi_gian < :n3_start_month)', 'nhap_cuoi_thang', array(':n3_start_month' => $end_month));
    //    $query->addExpression('(SELECT SUM(x3.so_luong) FROM {dt_san_pham_nguyen_chi_tiet} AS x3 WHERE sp.ma_thanh_pham = x3.ma_thanh_pham AND x3.thoi_gian < :x3_start_month)', 'nhap_nguyen_cuoi_thang', array(':x3_start_month' => $end_month));
    //    $query->addExpression('(SELECT SUM(xk3.xuat_kho) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk3 ,{dt_ban_hang} as bh3 WHERE xk3.ma_phieu_ban_hang=bh3.ma_phieu_ban_hang and sp.ma_thanh_pham = xk3.ma_thanh_pham AND bh3.thoi_gian < :bh3_start_month)', 'xuat_cuoi_thang', array(':bh3_start_month' => $end_month));
    //    $query->addExpression('(SELECT SUM(nlbht3.nhan_lai) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht3 WHERE sp.ma_thanh_pham = nlbht3.ma_thanh_pham AND nlbht3.thoi_gian < :nlbht3_start_month)', 'nlbht3_cuoi_thang', array(':nlbht3_start_month' => $end_month));
    //    $query->addExpression('(SELECT SUM(n3.so_luong) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n3 WHERE sp.ma_thanh_pham = n3.ma_vat_tu AND n3.thoi_gian < :n3_start_month)', 'nhap_vt_cuoi_thang', array(':n3_start_month' => $end_month));
    //    $query->addExpression('(SELECT SUM(x3.so_luong) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x3 WHERE sp.ma_thanh_pham = x3.ma_vat_tu AND x3.thoi_gian < :x3_start_month)', 'xuat_vt_cuoi_thang', array(':x3_start_month' => $end_month));
    //    $query->addExpression('(SELECT SUM(nh3.so_luong) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS nh3 WHERE sp.ma_thanh_pham = nh3.ma_vat_tu AND nh3.thoi_gian < :nh3_start_month)', 'xuat_tkt_vt_cuoi_thang', array(':nh3_start_month' => $end_month));
    //    $query->addExpression('(SELECT SUM(ntk3.so_luong) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk3 WHERE sp.ma_thanh_pham = ntk3.ma_vat_tu AND ntk3.thoi_gian < :ntk3_start_month)', 'nhap_tinh_vt_cuoi_thang', array(':ntk3_start_month' => $end_month));
    $result = $query->orderBy('sp.ten_thanh_pham')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
//        l($rs->ten_thanh_pham, 'san_pham/thong_ke_san_pham/chi_tiet_san_pham/den_ngay/' . $rs->ma_thanh_pham),
        array('data' => $rs->ten_thanh_pham, 'class' => array('text-center')),
        array('data' => number_format($rs->nhap_den_thang + $rs->nhap_nguyen_den_thang - $rs->xuat_den_thang + $rs->du - $rs->nlbht0_den_thang + $rs->nhap_vt_den_thang + $rs->nhap_tinh_vt_den_thang - $rs->xuat_vt_den_thang - $rs->xuat_tkt_vt_den_thang), 'class' => array('text-center')),
//        array('data' => number_format($rs->nhap_thang) ? number_format($rs->nhap_thang)
//          : '', 'class' => array('text-center')
//        ),
        $rs->nhap_thang
          ? l(number_format($rs->nhap_thang), 'san_pham/thong_ke_san_pham/chi_tiet/nhap/den_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->hoi_thang) ? number_format($rs->hoi_thang)
//          : '', 'class' => array('text-center')
//        ),
        $rs->hoi_thang
          ? l(number_format($rs->hoi_thang), 'san_pham/thong_ke_san_pham/chi_tiet/hoi/den_ngay/' . $rs->ma_thanh_pham)
          : '',
//        //array('data' => number_format($rs->xk_thang - $rs->km_thang - $rs->bh_thang)), //xuat
//        array('data' => number_format($rs->xk_thang - $rs->nlbht_thang)
//          ? number_format($rs->xk_thang - $rs->nlbht_thang) : '', 'class' => array('text-center')
//        ), //xuat - km - bh
        $rs->xk_thang - $rs->nlbht_thang
          ? l(number_format($rs->xk_thang - $rs->nlbht_thang), 'san_pham/thong_ke_san_pham/chi_tiet/xuat/den_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->km_thang) ? number_format($rs->km_thang)
//          : '', 'class' => array('text-center')
//        ), //khuyen mai
        $rs->km_thang
          ? l(number_format($rs->km_thang), 'san_pham/thong_ke_san_pham/chi_tiet/kmai/den_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->bh_thang + $rs->nbht_thang) ? number_format($rs->bh_thang + $rs->nbht_thang)
//          : '', 'class' => array('text-center')
//        ), //bao hanh
        $rs->bh_thang + $rs->nbht_thang
          ? l(number_format($rs->bh_thang + $rs->nbht_thang), 'san_pham/thong_ke_san_pham/chi_tiet/bh/den_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->nguyen_thang) ? number_format($rs->nguyen_thang)
//          : '', 'class' => array('text-center')
//        ),
        $rs->nguyen_thang
          ? l(number_format($rs->nguyen_thang), 'san_pham/thong_ke_san_pham/chi_tiet/nhapnguyen/den_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->nhap_ngay) ? number_format($rs->nhap_ngay)
//          : '', 'class' => array('text-center')
//        ),
        //   array('data' => number_format($rs->nhap_cuoi_thang + $rs->nhap_nguyen_cuoi_thang - $rs->xuat_cuoi_thang + $rs->du - $rs->nlbht3_cuoi_thang + $rs->nhap_vt_cuoi_thang + $rs->nhap_tinh_vt_cuoi_thang - $rs->xuat_vt_cuoi_thang - $rs->xuat_tkt_vt_cuoi_thang), 'class' => array('text-center')),
        $rs->nhap_ngay
          ? l(number_format($rs->nhap_ngay), 'san_pham/thong_ke_san_pham/chi_tiet/nhap/trong_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->hoi_ngay) ? number_format($rs->hoi_ngay)
//          : '', 'class' => array('text-center')
//        ),
        $rs->hoi_ngay
          ? l(number_format($rs->hoi_ngay), 'san_pham/thong_ke_san_pham/chi_tiet/hoi/trong_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->xk_ngay - $rs->nlbht_ngay) ? number_format($rs->xk_ngay - $rs->nlbht_ngay)
//          : '', 'class' => array('text-center')
//        ),
        $rs->xk_ngay - $rs->nlbht_ngay
          ? l(number_format($rs->xk_ngay - $rs->nlbht_ngay), 'san_pham/thong_ke_san_pham/chi_tiet/xuat/trong_ngay/' . $rs->ma_thanh_pham)
          : '', //xuatkho - km - bh
//        array('data' => number_format($rs->km_ngay) ? number_format($rs->km_ngay) : '', 'class' => array('text-center')
//        ),
        $rs->km_ngay
          ? l(number_format($rs->km_ngay), 'san_pham/thong_ke_san_pham/chi_tiet/kmai/trong_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->bh_ngay + $rs->nbht_ngay) ? number_format($rs->bh_ngay + $rs->nbht_ngay)
//          : '', 'class' => array('text-center')
//        ),
        $rs->bh_ngay + $rs->nbht_ngay
          ? l(number_format($rs->bh_ngay + $rs->nbht_ngay), 'san_pham/thong_ke_san_pham/chi_tiet/bh/trong_ngay/' . $rs->ma_thanh_pham)
          : '',
//        array('data' => number_format($rs->nguyen_ngay) ? number_format($rs->nguyen_ngay)
//          : '', 'class' => array('text-center')
//        ),
        $rs->nguyen_ngay
          ? l(number_format($rs->nguyen_ngay), 'san_pham/thong_ke_san_pham/chi_tiet/nhapnguyen/trong_ngay/' . $rs->ma_thanh_pham)
          : '',
      );
    }
    return $data;
  }

  function get_thong_ke_san_pham_thang($start_month, $end_month) {
    $query = db_select('dt_san_pham', 'sp');
    $query->fields('sp', array('id', 'ma_thanh_pham', 'ten_thanh_pham', 'thoi_gian'));
    $query->condition('sp.hien_thi', 0);
    $query->addExpression('(SELECT SUM(n0.so_luong) FROM {dt_san_pham_nhap_chi_tiet} AS n0 WHERE sp.ma_thanh_pham = n0.ma_thanh_pham AND n0.thoi_gian < :n0_start_month)', 'nhap_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(x0.so_luong) FROM {dt_san_pham_nguyen_chi_tiet} AS x0 WHERE sp.ma_thanh_pham = x0.ma_thanh_pham AND x0.thoi_gian < :x0_start_month)', 'nhap_nguyen_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(xk0.xuat_kho) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk0 ,{dt_ban_hang} as bh0 WHERE xk0.ma_phieu_ban_hang=bh0.ma_phieu_ban_hang and sp.ma_thanh_pham = xk0.ma_thanh_pham AND bh0.thoi_gian < :bh0_start_month)', 'xuat_den_thang', array(':bh0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(d.so_luong) FROM {dt_san_pham_du_lieu} AS d WHERE sp.ma_thanh_pham = d.ma_thanh_pham AND d.thoi_gian < :d_start_month)', 'du', array(':d_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(nlbht0.nhan_lai) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht0 WHERE sp.ma_thanh_pham = nlbht0.ma_thanh_pham AND nlbht0.thoi_gian < :nlbht0_start_month)', 'nlbht0_den_thang', array(':nlbht0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(n0.so_luong) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n0 WHERE sp.ma_thanh_pham = n0.ma_vat_tu AND n0.thoi_gian < :n0_start_month)', 'nhap_vt_den_thang', array(':n0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(x0.so_luong) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x0 WHERE sp.ma_thanh_pham = x0.ma_vat_tu AND x0.thoi_gian < :x0_start_month)', 'xuat_vt_den_thang', array(':x0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(nh0.so_luong) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS nh0 WHERE sp.ma_thanh_pham = nh0.ma_vat_tu AND nh0.thoi_gian < :nh0_start_month)', 'xuat_tkt_vt_den_thang', array(':nh0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(ntk0.so_luong) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk0 WHERE sp.ma_thanh_pham = ntk0.ma_vat_tu AND ntk0.thoi_gian < :ntk0_start_month)', 'nhap_tinh_vt_den_thang', array(':ntk0_start_month' => $start_month));
    $query->addExpression('(SELECT SUM(n1.so_luong) FROM {dt_san_pham_nhap_chi_tiet} AS n1 WHERE sp.ma_thanh_pham = n1.ma_thanh_pham AND n1.thoi_gian >= :n1_start_month AND n1.thoi_gian <= :n1_end_month)', 'nhap_thang', array(':n1_start_month' => $start_month, ':n1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(x1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS x1 WHERE sp.ma_thanh_pham = x1.ma_thanh_pham AND x1.thoi_gian >= :x1_start_month AND x1.thoi_gian <= :x1_end_month)', 'hoi_thang', array(':x1_start_month' => $start_month, ':x1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(h1.so_luong) FROM {dt_san_pham_nguyen_chi_tiet} AS h1 WHERE sp.ma_thanh_pham = h1.ma_thanh_pham AND h1.thoi_gian >= :h1_start_month AND h1.thoi_gian <= :h1_end_month)', 'nguyen_thang', array(':h1_start_month' => $start_month, ':h1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(b1.so_luong) FROM {dt_san_pham_hoi_chi_tiet} AS b1 ,{dt_san_pham_hoi} AS h1 WHERE b1.ma_phieu_hang_hoi = h1.ma_phieu_hang_hoi and h1.trang_thai_bao_hanh =1 and b1.don_gia_tru_tien = 0 and sp.ma_thanh_pham = b1.ma_thanh_pham AND b1.thoi_gian_tra_bh <> 0 AND b1.thoi_gian_tra_bh >= :b1_start_month AND b1.thoi_gian_tra_bh <= :b1_end_month)', 'bh_thang', array(':b1_start_month' => $start_month, ':b1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(k1.so_luong) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS k1 WHERE sp.ma_thanh_pham = k1.ma_thanh_pham and k1.tru_tien=0 AND k1.thoi_gian >= :k1_start_month AND k1.thoi_gian <= :k1_end_month)', 'km_thang', array(':k1_start_month' => $start_month, ':k1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(xk1.xuat_kho) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk1 ,{dt_ban_hang} as bh1 WHERE xk1.ma_phieu_ban_hang=bh1.ma_phieu_ban_hang and sp.ma_thanh_pham = xk1.ma_thanh_pham  AND bh1.thoi_gian >= :bh1_start_month AND bh1.thoi_gian <= :bh1_end_month)', 'xk_thang', array(':bh1_start_month' => $start_month, ':bh1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(nbht1.so_no) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nbht1 WHERE sp.ma_thanh_pham = nbht1.ma_thanh_pham AND nbht1.thoi_gian >= :nbht1_start_month AND nbht1.thoi_gian <= :nbht1_end_month)', 'nbht_thang', array(':nbht1_start_month' => $start_month, ':nbht1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(nlbht1.nhan_lai) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht1 WHERE sp.ma_thanh_pham = nlbht1.ma_thanh_pham AND nlbht1.thoi_gian >= :nlbht1_start_month AND nlbht1.thoi_gian <= :nlbht1_end_month)', 'nlbht_thang', array(':nlbht1_start_month' => $start_month, ':nlbht1_end_month' => $end_month));
    $query->addExpression('(SELECT SUM(n2.so_luong) FROM {dt_san_pham_nhap_chi_tiet} AS n2 WHERE sp.ma_thanh_pham = n2.ma_thanh_pham AND n2.thoi_gian < :n2_start_month)', 'nhap_cuoi_thang', array(':n2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(x2.so_luong) FROM {dt_san_pham_nguyen_chi_tiet} AS x2 WHERE sp.ma_thanh_pham = x2.ma_thanh_pham AND x2.thoi_gian < :x2_start_month)', 'nhap_nguyen_cuoi_thang', array(':x2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(xk2.xuat_kho) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk2 ,{dt_ban_hang} as bh2 WHERE xk2.ma_phieu_ban_hang=bh2.ma_phieu_ban_hang and sp.ma_thanh_pham = xk2.ma_thanh_pham AND bh2.thoi_gian < :bh2_start_month)', 'xuat_cuoi_thang', array(':bh2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(nlbht2.nhan_lai) FROM {dt_ban_hang_chi_tiet_bao_hanh_truoc} AS nlbht2 WHERE sp.ma_thanh_pham = nlbht2.ma_thanh_pham AND nlbht2.thoi_gian < :nlbht2_start_month)', 'nlbht2_cuoi_thang', array(':nlbht2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(n2.so_luong) FROM {dt_vat_tu_phieu_nhap_chi_tiet} AS n2 WHERE sp.ma_thanh_pham = n2.ma_vat_tu AND n2.thoi_gian < :n2_start_month)', 'nhap_vt_cuoi_thang', array(':n2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(x2.so_luong) FROM {dt_vat_tu_phieu_xuat_chi_tiet} AS x2 WHERE sp.ma_thanh_pham = x2.ma_vat_tu AND x2.thoi_gian < :x2_start_month)', 'xuat_vt_cuoi_thang', array(':x2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(nh2.so_luong) FROM {dt_vat_tu_phieu_xuat_chi_tiet_tkt} AS nh2 WHERE sp.ma_thanh_pham = nh2.ma_vat_tu AND nh2.thoi_gian < :nh2_start_month)', 'xuat_tkt_vt_cuoi_thang', array(':nh2_start_month' => $end_month));
    $query->addExpression('(SELECT SUM(ntk2.so_luong) FROM {dt_vat_tu_phieu_nhap_tinh_kho_chi_tiet} AS ntk2 WHERE sp.ma_thanh_pham = ntk2.ma_vat_tu AND ntk2.thoi_gian < :ntk2_start_month)', 'nhap_tinh_vt_cuoi_thang', array(':ntk2_start_month' => $end_month));
    $result = $query->orderBy('sp.ten_thanh_pham')->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
//       / l($rs->ten_thanh_pham, 'san_pham/thong_ke_san_pham/chi_tiet_san_pham/trong_thang/' . $rs->ma_thanh_pham),
        array('data' => $rs->ten_thanh_pham, 'class' => array('text-center')),
        array('data' => number_format($rs->nhap_den_thang + $rs->nhap_nguyen_den_thang - $rs->xuat_den_thang + $rs->du - $rs->nlbht0_den_thang + $rs->nhap_vt_den_thang + $rs->nhap_tinh_vt_den_thang - $rs->xuat_vt_den_thang - $rs->xuat_tkt_vt_den_thang), 'class' => array('text-center')),
        // array('data' => number_format($rs->nhap_thang) ? number_format($rs->nhap_thang) : '', 'class' => array('text-center')),
        $rs->nhap_thang
          ? l(number_format($rs->nhap_thang), 'san_pham/thong_ke_san_pham/chi_tiet/nhap/trong_thang/' . $rs->ma_thanh_pham)
          : '',
        // array('data' => number_format($rs->hoi_thang) ? number_format($rs->hoi_thang) : '', 'class' => array('text-center')),
        $rs->hoi_thang
          ? l(number_format($rs->hoi_thang), 'san_pham/thong_ke_san_pham/chi_tiet/hoi/trong_thang/' . $rs->ma_thanh_pham)
          : '',
        // array('data' => number_format($rs->xk_thang - $rs->nlbht_thang)
        //   ? number_format($rs->xk_thang - $rs->nlbht_thang) : '', 'class' => array('text-center')
        // ), //xuatkho
        $rs->xk_thang - $rs->nlbht_thang
          ? l(number_format($rs->xk_thang - $rs->nlbht_thang), 'san_pham/thong_ke_san_pham/chi_tiet/xuat/trong_thang/' . $rs->ma_thanh_pham)
          : '',
        //array('data' => number_format($rs->km_thang) ? number_format($rs->km_thang)
        //  : '', 'class' => array('text-center')
        //),
        $rs->km_thang
          ? l(number_format($rs->km_thang), 'san_pham/thong_ke_san_pham/chi_tiet/kmai/trong_thang/' . $rs->ma_thanh_pham)
          : '',
        //array('data' => number_format($rs->bh_thang + $rs->nbht_thang) ? number_format($rs->bh_thang + $rs->nbht_thang)
        //  : '', 'class' => array('text-center')
        //),
        $rs->bh_thang + $rs->nbht_thang
          ? l(number_format($rs->bh_thang + $rs->nbht_thang), 'san_pham/thong_ke_san_pham/chi_tiet/bh/trong_thang/' . $rs->ma_thanh_pham)
          : '',
        //array('data' => number_format($rs->nguyen_thang) ? number_format($rs->nguyen_thang) : '', 'class' => array('text-center')),
        $rs->nguyen_thang
          ? l(number_format($rs->nguyen_thang), 'san_pham/thong_ke_san_pham/chi_tiet/nhapnguyen/trong_thang/' . $rs->ma_thanh_pham)
          : '',
        array('data' => number_format($rs->nhap_cuoi_thang + $rs->nhap_nguyen_cuoi_thang - $rs->xuat_cuoi_thang + $rs->du - $rs->nlbht2_cuoi_thang + $rs->nhap_vt_cuoi_thang + $rs->nhap_tinh_vt_cuoi_thang - $rs->xuat_vt_cuoi_thang - $rs->xuat_tkt_vt_cuoi_thang), 'class' => array('text-center')),
      );
    }
    return $data;
  }

  function get_thong_ke_chi_tiet_san_pham($thanh_pham, $start_time, $end_time) {
    $query_n = db_select('dt_san_pham_nhap_chi_tiet', 'n');
    $query_n->fields('n', array('id', 'thoi_gian', 'so_luong'));
    $query_n->addField('n', 'ma_phieu_nhap', 'type');
    $query_n->condition('n.ma_thanh_pham', $thanh_pham->ma_thanh_pham, '=');
    $query_n->condition('n.thoi_gian', $start_time, '>=');
    $query_n->condition('n.thoi_gian', $end_time, '<=');
    $query_x = db_select('dt_san_pham_hoi_chi_tiet', 'x');
    $query_x->fields('x', array('id', 'thoi_gian'));
    $query_x->addField('x', 'so_luong');
    $query_x->addExpression("'PNTPHT'", 'type');
    $query_x->condition('x.ma_thanh_pham', $thanh_pham->ma_thanh_pham, '=');
    $query_x->condition('x.thoi_gian', $start_time, '>=');
    $query_x->condition('x.thoi_gian', $end_time, '<=');
    $query_x->condition('x.don_gia_tru_tien', 0, '=');
    $query_h = db_select('dt_san_pham_nguyen_chi_tiet', 'h');
    $query_h->fields('h', array('id', 'thoi_gian', 'so_luong'));
    $query_h->addField('h', 'ma_phieu_hang_nguyen', 'type');
    $query_h->condition('h.ma_thanh_pham', $thanh_pham->ma_thanh_pham, '=');
    $query_h->condition('h.thoi_gian', $start_time, '>=');
    $query_h->condition('h.thoi_gian', $end_time, '<=');
    $query_k = db_select('dt_ban_hang_chi_tiet_khuyen_mai', 'k');
    $query_k->fields('k', array('id', 'thoi_gian', 'so_luong'));
    $query_k->addField('k', 'ma_phieu_ban_hang', 'type');
    $query_k->condition('k.ma_thanh_pham', $thanh_pham->ma_thanh_pham, '=');
    $query_k->condition('k.thoi_gian', $start_time, '>=');
    $query_k->condition('k.thoi_gian', $end_time, '<=');
    $query_k->condition('k.tru_tien', 0, '=');
    //xuat kho
    $query_bh = db_select('dt_ban_hang', 'bh');
    $query_bh->fields('bh', array('id', 'thoi_gian'));
    $query_bh->addExpression('(SELECT SUM(xk.xuat_kho) FROM {dt_ban_hang_chi_tiet_so_luong} AS xk WHERE xk.ma_phieu_ban_hang=bh.ma_phieu_ban_hang and xk.ma_thanh_pham = :ma_thanh_pham  AND  bh.thoi_gian >= :bh_start_month AND bh.thoi_gian <= :bh_end_month)', 'so_luong', array(':ma_thanh_pham' => $thanh_pham->ma_thanh_pham, ':bh_start_month' => $start_time, ':bh_end_month' => $end_time));
    $query_bh->addExpression("'PBHB'", 'type');
    //    $query_b = db_select('dt_san_pham_hoi_chi_tiet', 'b');
    //    $query_b->fields('b', array('id', 'thoi_gian'));
    //    $query_b->addExpression('(SELECT SUM(b.so_luong) FROM {dt_san_pham_hoi} AS h WHERE b.ma_phieu_hang_hoi = h.ma_phieu_hang_hoi and h.trang_thai_bao_hanh =1 and b.ma_thanh_pham = :ma_thanh_pham AND b.thoi_gian >= :b_start_month AND b.thoi_gian <= :b_end_month)', 'so_luong', array(':ma_thanh_pham'=>$thanh_pham->ma_thanh_pham,':b_start_month' => $start_time, ':b_end_month' => $end_time));
    //    $query_b->addExpression("'PNTPHX'",'type');
    $query_b = db_select('dt_san_pham_hoi_chi_tiet', 'b');
    $query_b->fields('b', array('id', 'thoi_gian', 'so_luong'));
    $query_b->leftJoin('dt_san_pham_hoi', 'h', 'b.ma_phieu_hang_hoi = h.ma_phieu_hang_hoi');
    $query_b->condition('h.trang_thai_bao_hanh', 1, '=');
    //$query_b->addField('b', 'ma_phieu_hang_hoi', 'type');
    $query_b->addExpression("'PNTPHX'", 'type');
    $query_b->condition('b.ma_thanh_pham', $thanh_pham->ma_thanh_pham, '=');
    $query_b->condition('b.thoi_gian', $start_time, '>=');
    $query_b->condition('b.thoi_gian', $end_time, '<=');
    $query_n->union($query_x, 'UNION ALL');
    $query_n->union($query_h, 'UNION ALL');
    $query_n->union($query_k, 'UNION ALL');
    $query_n->union($query_bh, 'UNION ALL');
    $query_n->union($query_b, 'UNION ALL');
    $query = db_select($query_n, 'vt')->extend('PagerDefault');
    $query->fields('vt', array('type', 'so_luong', 'thoi_gian'));
    $query->orderBy('thoi_gian', 'DESC');
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $type = $rs->type;
      if ($type[1] == 'B') {
        if ($type[3] == 'B') {
          $type = $type[3]; // B -> PBHB
        }
        else {
          $type = $type[2]; // H -> PBH
        }
      }
      else {
        //        if($type[5] == 'X'){
        //          $type=$type[5];  // x -> PNTPHX
        //        }
        //        else{
        //          if($type[4]== '_'){
        //           $type=$type[3]; // p -> pntp
        //          }
        //          else
        //          {
        //           $type=$type[4];
        //          }
        //        }
        if ($type[4] == '_') {
          $type = $type[3]; // p -> pntp
        }
        else {
          if ($type[4] == 'N') {
            $type = $type[4]; //N -> PNTPN
          }
          ELSE {
            $type = $type[5];
          }
        }
      }
      $date = date('H:i:s d/m/Y', $rs->thoi_gian);
      if (!isset($data[$date])) {
        $data[$date] = array(
          'D' => array('data' => $date, 'class' => array('text-center')),
          'P' => array('data' => '', 'class' => array('text-center')),
          'T' => array('data' => '', 'class' => array('text-center')),
          'B' => array('data' => '', 'class' => array('text-center')),
          'H' => array('data' => '', 'class' => array('text-center')),
          'X' => array('data' => '', 'class' => array('text-center')),
          'N' => array('data' => '', 'class' => array('text-center')),
        );
      }
      if (!$data[$date][$type]['data']) {
        $data[$date][$type]['data'] = $rs->so_luong;
      }
      else {
        $data[$date][$type]['data'] += $rs->so_luong;
      }
    }
    return $data;
  }

  function update_so_luong_san_pham($sign, $so_luong, $ma_thanh_pham) {
    db_update('dt_san_pham')
      ->expression('so_luong', 'so_luong ' . $sign . ' :so_luong', array(':so_luong' => $so_luong))
      ->condition('ma_thanh_pham', $ma_thanh_pham)
      ->execute();
    db_update('dt_vat_tu')
      ->expression('so_luong', 'so_luong ' . $sign . ' :so_luong', array(':so_luong' => $so_luong))
      ->condition('ma_vat_tu', $ma_thanh_pham)
      ->execute();
  }

  function update_so_luong_san_pham_hong($sign, $so_luong, $ma_thanh_pham) {
    db_update('dt_san_pham')
      ->expression('so_luong_hong', 'so_luong_hong ' . $sign . ' :so_luong_hong', array(':so_luong_hong' => $so_luong))
      ->condition('ma_thanh_pham', $ma_thanh_pham)
      ->execute();
    db_update('dt_vat_tu')
      ->expression('so_luong_hong', 'so_luong_hong ' . $sign . ' :so_luong_hong', array(':so_luong_hong' => $so_luong))
      ->condition('ma_vat_tu', $ma_thanh_pham)
      ->execute();
  }

  function get_thiet_lap_khuyen_mai_thang($header) {
    $query = db_select('dt_khuyen_mai_thang', 'km')->extend('TableSort');
    $query->condition('km.trang_thai', 1);
    $query->innerJoin('dt_loai_san_pham', 'sp', 'sp.id = km.loai_san_pham');
    $query->fields('km', array('id', 'loai_san_pham', 'loai_khach_hang', 'tong_so_luong_thang', 'so_luong_khuyen_mai', 'trang_thai'));
    $query->addField('sp', 'loai_san_pham', 'ten_loai_san_pham');
    $query->orderByHeader($header);
    $result = $query->orderBy('km.loai_khach_hang', 'ASC')->orderBy('km.tong_so_luong_thang', 'ASC')->execute();
    $data = array();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
      $link = $rs->trang_thai ? l('Ngưng khuyến mại', 'san_pham/cap_nhat_khuyen_mai_thang/ngung/' . $rs->id)
        : l('Tiếp tục', 'san_pham/cap_nhat_khuyen_mai_thang/tiep_tuc/' . $rs->id);
      $data[] = array(
        $i,
        $rs->ten_loai_san_pham,
        $rs->loai_khach_hang ? 'Đặc biệt' : 'Bình thường',
        $rs->tong_so_luong_thang,
        $rs->so_luong_khuyen_mai,
        $rs->trang_thai ? 'Tiếp tục' : 'Ngừng',
        '<a href="javascript:;" onclick="show_sua_khuyen_mai_thang(' . $rs->id . ', ' . $rs->loai_san_pham . ', ' . $rs->loai_khach_hang . ', ' . $rs->tong_so_luong_thang . ', ' . $rs->so_luong_khuyen_mai . ')">Sửa</a> | ' . $link,
      );
    }
    return $data;
  }

  function update_trang_thai_khuyen_mai_thang($id, $trang_thai) {
    db_update('dt_khuyen_mai_thang')
      ->fields(array('trang_thai' => $trang_thai))
      ->condition('id', $id)
      ->execute();
  }

  function get_option_loai_san_pham($to_string = false) {
    $query = db_select('dt_loai_san_pham', 'lsp');
    $query->fields('lsp', array('id', 'loai_san_pham'));
    $result = $query->execute();
    if ($to_string) {
      $str_loai = '';
      while ($row = $result->fetchObject())
      {
        $str_loai .= '<option value="' . $row->id . '">' . $row->loai_san_pham . '</option>';
      }
      return $str_loai;
    }
    else {
      $list_loai = array();
      while ($row = $result->fetchObject())
      {
        $list_loai[$row->id] = $row;
      }
      return $list_loai;
    }
  }

  function check_thiet_lap_khuyen_mai_thang($loai_san_pham, $tong_so_luong_thang, $so_luong_khuyen_mai, $loai_khach_hang, $type, $id_km_thang = '') {
    $query = db_select('dt_khuyen_mai_thang', 'km');
    $query->fields('km', array('id'));
    $query->condition('km.loai_san_pham', $loai_san_pham);
    $query->condition('km.loai_khach_hang', $loai_khach_hang);
    if ($type == 0) {
      $query->condition('km.tong_so_luong_thang', $tong_so_luong_thang, '<=');
      $query->condition('km.so_luong_khuyen_mai', $so_luong_khuyen_mai, '>=');
    }
    else {
      $query->condition('km.tong_so_luong_thang', $tong_so_luong_thang, '>');
      $query->condition('km.so_luong_khuyen_mai', $so_luong_khuyen_mai, '<=');
    }
    $query->condition('km.trang_thai', 1);
    if ($id_km_thang) {
      $query->condition('km.id', $id_km_thang, '<>');
    }
    return $query->execute()->fetchObject();
  }

  function update_thiet_lap_khuyen_mai_thang($loai_san_pham, $loai_khach_hang, $tong_so_luong_thang, $so_luong_khuyen_mai, $id_km_thang = '') {
    db_update('dt_khuyen_mai_thang')
      ->fields(array(
                    'loai_san_pham' => $loai_san_pham,
                    'loai_khach_hang' => $loai_khach_hang,
                    'tong_so_luong_thang' => $tong_so_luong_thang,
                    'so_luong_khuyen_mai' => $so_luong_khuyen_mai,
               ))
      ->condition('id', $id_km_thang)
      ->execute();
  }

  function insert_thiet_lap_khuyen_mai_thang($loai_san_pham, $loai_khach_hang, $tong_so_luong_thang, $so_luong_khuyen_mai) {
    global $user;
    $thoi_gian = time();
    $trang_thai = 1;
    $query = db_insert('dt_khuyen_mai_thang');
    $arr = array(
      'uid' => $user->uid,
      'loai_san_pham' => $loai_san_pham,
      'loai_khach_hang' => $loai_khach_hang,
      'tong_so_luong_thang' => $tong_so_luong_thang,
      'so_luong_khuyen_mai' => $so_luong_khuyen_mai,
      'trang_thai' => $trang_thai,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    return $query->execute();
  }

  function load_hang_duoc_bao_hanh($ma_khach_hang) {
    $query = db_select('dt_san_pham_hoi', 'p');
    $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = p.ma_khach_hang');
    $query->innerJoin('dt_san_pham_hoi_chi_tiet', 'ct', 'ct.ma_phieu_hang_hoi = p.ma_phieu_hang_hoi');
    $query->innerJoin('dt_san_pham', 'sp', 'ct.ma_thanh_pham = sp.ma_thanh_pham');
    $query->fields('kh', array('loai'));
    $query->fields('p', array('ghi_chu'));
    $query->fields('ct', array('id', 'ma_phieu_hang_hoi', 'ma_thanh_pham', 'so_luong', 'so_luong_hoi'));
    $query->fields('sp', array('ten_thanh_pham', 'gia_tien', 'idloai'));
    $query->condition('p.ma_khach_hang', $ma_khach_hang);
    $query->condition('p.trang_thai_bao_hanh', 0);
    $result = $query->execute();
    $data['duoc_bao_hanh'] = array();
    $data['ghi_chu'] = array();
    $tru_tien = variable_get('tru_tien_khach_hang_dac_biet', 500);
    while ($duoc_bao_hanh = $result->fetchObject()) {
      if ($duoc_bao_hanh->loai) {
        if ($duoc_bao_hanh->idloai == 1) {
          $duoc_bao_hanh->gia_tien = $duoc_bao_hanh->gia_tien - $tru_tien;
        }
      }
      $input = '<input type="checkbox" name="tra_hang_duoc_bao_hanh[' . $duoc_bao_hanh->id . ']" class="form-checkbox checkbox txt-tra-hang-duoc-bao-hanh tra-hang tra-hang-' . $duoc_bao_hanh->ma_phieu_hang_hoi . '" value="' . $duoc_bao_hanh->ma_phieu_hang_hoi . '" />';
      $data['duoc_bao_hanh'][] = array(
        $input,
        '<label class="data-text ma-phieu-hang-hoi">' . $duoc_bao_hanh->ma_phieu_hang_hoi . '</label>',
        '<label class="data-text ten-hang-duoc-bao-hanh">' . $duoc_bao_hanh->ten_thanh_pham . ' [' . $duoc_bao_hanh->ma_thanh_pham . ']' . '</label>',
        '<label class="data-text so-luong-hang-mang-den">' . $duoc_bao_hanh->so_luong_hoi . '</label>',
        '<label class="data-text so-luong-hang-duoc-bao-hanh">' . $duoc_bao_hanh->so_luong . '</label>',
        '<label class="data-text lbl-don-gia-hang-duoc-bao-hanh">' . number_format($duoc_bao_hanh->gia_tien) . '</label>',
        '<input type="checkbox" name="tru_tien_hang_duoc_bao_hanh[' . $duoc_bao_hanh->id . ']" class="form-checkbox checkbox txt-tru-tien-hang-duoc-bao-hanh tru-tien" value="' . $duoc_bao_hanh->ma_thanh_pham . '" />
        <input type="text" name="don_gia_tru_tien_hang_duoc_bao_hanh[' . $duoc_bao_hanh->id . ']" class="form-text text txt-don-gia-tru-tien-hang-duoc-bao-hanh format-currency" autocomplete="OFF" value="" />
        <input type="hidden" name="ten_thanh_pham_hang_duoc_bao_hanh[' . $duoc_bao_hanh->id . ']" value="' . $duoc_bao_hanh->ten_thanh_pham . ' [' . $duoc_bao_hanh->ma_thanh_pham . ']' . '" />
        <input type="hidden" name="gia_tien_hang_duoc_bao_hanh[' . $duoc_bao_hanh->id . ']" value="' . $duoc_bao_hanh->gia_tien . '" />
        <input type="hidden" name="so_luong_hang_mang_den[' . $duoc_bao_hanh->id . ']" value="' . $duoc_bao_hanh->so_luong_hoi . '" />
        <input type="hidden" name="so_luong_hang_duoc_bao_hanh[' . $duoc_bao_hanh->id . ']" value="' . $duoc_bao_hanh->so_luong . '" />
        <input type="hidden" name="ma_hang_duoc_bao_hanh[' . $duoc_bao_hanh->id . ']" value="' . $duoc_bao_hanh->ma_thanh_pham . '" />
        <input type="hidden" name="mp_hang_duoc_bao_hanh[' . $duoc_bao_hanh->id . ']" value="' . $duoc_bao_hanh->ma_phieu_hang_hoi . '" />'
      );
      if ($duoc_bao_hanh->ghi_chu) {
        $data['ghi_chu'][$duoc_bao_hanh->ma_phieu_hang_hoi] = $duoc_bao_hanh->ghi_chu;
      }
    }
    return $data;
  }

  function load_hang_no_bht($ma_khach_hang) {
    $query = db_select('dt_san_pham_hoi', 'p');
    $query->innerJoin('dt_ban_hang_chi_tiet_bao_hanh_truoc', 'ct', 'ct.ma_phieu_hang_hoi = p.ma_phieu_hang_hoi');
    $query->innerJoin('dt_san_pham', 'sp', 'ct.ma_thanh_pham = sp.ma_thanh_pham');
    $query->fields('p', array('ghi_chu'));
    $query->fields('ct', array('id', 'ma_phieu_hang_hoi', 'ma_phieu_ban_hang', 'ma_thanh_pham', 'so_luong', 'nhan_lai', 'so_no', 'tra_no'));
    $query->fields('sp', array('ten_thanh_pham', 'gia_tien'));
    $query->condition('p.ma_khach_hang', $ma_khach_hang);
    $query->condition('ct.so_no', 0, '<>');
    $query->condition('ct.tra_no', 0, '=');
    $result = $query->execute();
    $data['hang_no_bht'] = array();
    $data['ghi_chu'] = array();
    while ($hang_no_bht = $result->fetchObject()) {
      $data['hang_no_bht'][] = array(
        '<label class="data-text ma-phieu-ban-hang">' . $hang_no_bht->ma_phieu_ban_hang . '</label>',
        '<label class="data-text ten-hang-no-bht">' . $hang_no_bht->ten_thanh_pham . ' [' . $hang_no_bht->ma_thanh_pham . ']' . '</label>',
        '<label class="data-text so-luong-hang-mang-di">' . $hang_no_bht->so_luong . '</label>',
        '<label class="data-text so-luong-hang-nhan-lai">' . $hang_no_bht->nhan_lai . '</label>',
        '<label class="data-text so-luong-hang-khong-duoc-bao-hanh">' . $hang_no_bht->so_no . '</label>',
        '<label class="data-text so-luong-hang-tra-no">' . $hang_no_bht->tra_no . '</label>
        <input type="hidden" name="ten_thanh_pham_hang_no_bht[' . $hang_no_bht->id . ']" value="' . $hang_no_bht->ten_thanh_pham . ' [' . $hang_no_bht->ma_thanh_pham . ']' . '" />
        <input type="hidden" name="so_luong_hang_mang_di[' . $hang_no_bht->id . ']" value="' . $hang_no_bht->so_luong . '" />
        <input type="hidden" name="so_luong_hang_nhan_lai[' . $hang_no_bht->id . ']" value="' . $hang_no_bht->nhan_lai . '" />
        <input type="hidden" name="so_luong_hang_khong_duoc_bao_hanh[' . $hang_no_bht->id . ']" value="' . $hang_no_bht->so_no . '" />
        <input type="hidden" name="so_luong_hang_tra_no[' . $hang_no_bht->id . ']" value="' . $hang_no_bht->tra_no . '" />
        <input type="hidden" name="so_luong_hang_dang_no[' . $hang_no_bht->id . ']" value="' . ($hang_no_bht->so_no - $hang_no_bht->tra_no) . '" />
        <input type="hidden" name="ma_hang_no_bht[' . $hang_no_bht->id . ']" value="' . $hang_no_bht->ma_thanh_pham . '" />
        <input type="hidden" name="mp_hang_no_bht[' . $hang_no_bht->id . ']" value="' . $hang_no_bht->ma_phieu_ban_hang . '" />'
      );
      if ($hang_no_bht->ghi_chu) {
        $data['ghi_chu'][$hang_no_bht->ma_phieu_ban_hang] = $hang_no_bht->ghi_chu;
      }
    }
    return $data;
  }

  function load_hang_no_cu($ma_khach_hang) {
    $query = db_select('dt_hang_no', 'hn');
    $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = hn.ma_khach_hang');
    $query->innerJoin('dt_san_pham', 'sp', 'hn.ma_thanh_pham = sp.ma_thanh_pham');
    $query->fields('kh', array('loai'));
    $query->fields('hn', array('id', 'ma_thanh_pham', 'so_luong'));
    $query->fields('sp', array('ten_thanh_pham', 'gia_tien', 'idloai'));
    $query->condition('hn.ma_khach_hang', $ma_khach_hang);
    $result = $query->execute();
    $rows_hnc = array();
    $tru_tien = variable_get('tru_tien_khach_hang_dac_biet', 500);
    while ($hang_no_cu = $result->fetchObject()) {
      if ($hang_no_cu->loai) {
        if ($hang_no_cu->idloai == 1) {
          $hang_no_cu->gia_tien = $hang_no_cu->gia_tien - $tru_tien;
        }
      }
      $rows_hnc[] = array(
        '<label class="data-text ten-hang-no-cu">' . $hang_no_cu->ten_thanh_pham . ' [' . $hang_no_cu->ma_thanh_pham . ']' . '</label>',
        '<label class="data-text so-luong-hang-no-cu">' . $hang_no_cu->so_luong . '</label>',
        '<label class="data-text lbl-don-gia-hang-no-cu">' . number_format($hang_no_cu->gia_tien) . '</label>',
        '<input type="checkbox" name="tru_tien_hang_no_cu[' . $hang_no_cu->ma_thanh_pham . ']" class="form-checkbox checkbox txt-tru-tien-hang-no-cu tru-tien" value="' . $hang_no_cu->ma_thanh_pham . '" />
        <input type="text" name="don_gia_tru_tien_hang_no_cu[' . $hang_no_cu->ma_thanh_pham . ']" class="form-text text txt-don-gia-tru-tien-hang-no-cu format-currency" autocomplete="OFF" value="" />
        <input type="hidden" name="ten_thanh_pham_hang_no_cu[' . $hang_no_cu->ma_thanh_pham . ']" value="' . $hang_no_cu->ten_thanh_pham . ' [' . $hang_no_cu->ma_thanh_pham . ']' . '" />
        <input type="hidden" name="gia_tien_hang_no_cu[' . $hang_no_cu->ma_thanh_pham . ']" value="' . $hang_no_cu->gia_tien . '" />
        <input type="hidden" name="so_luong_hang_no_cu[' . $hang_no_cu->ma_thanh_pham . ']" value="' . $hang_no_cu->so_luong . '" />'
      );
    }
    return $rows_hnc;
  }

  function load_khuyen_mai($arr_ma, $ma_kh, $loai_kh, $ref_arr) {
    $month = date('n');
    $year = date('Y');
    $start_month = mktime(0, 0, 0, $month, 1, $year);
    $end_month = mktime(0, 0, 0, $month + 1, 1, $year) - 1;
    $query = db_select('dt_san_pham', 'sp');
    $query->fields('sp', array('ma_thanh_pham', 'ten_thanh_pham', 'gia_tien', 'so_luong', 'idloai'));
    $query->condition('sp.ma_thanh_pham', $arr_ma, 'in');
    $query->addExpression('(SELECT SUM(hm.so_luong) FROM {dt_ban_hang_chi_tiet_hang_mua} AS hm INNER JOIN {dt_ban_hang} AS bh1 ON bh1.ma_phieu_ban_hang = hm.ma_phieu_ban_hang WHERE sp.ma_thanh_pham = hm.ma_thanh_pham AND bh1.ma_khach_hang = :ma_kh_1 AND hm.thoi_gian >= :start_month_1 AND hm.thoi_gian <= :end_month_1)', 'tong_mua_thang', array(':ma_kh_1' => $ma_kh, ':start_month_1' => $start_month, ':end_month_1' => $end_month));
    $query->addExpression('(SELECT SUM(km.so_luong) FROM {dt_ban_hang_chi_tiet_khuyen_mai} AS km INNER JOIN {dt_ban_hang} AS bh2 ON bh2.ma_phieu_ban_hang = km.ma_phieu_ban_hang WHERE sp.ma_thanh_pham = km.ma_thanh_pham AND bh2.ma_khach_hang = :ma_kh_2 AND km.thoi_gian >= :start_month_2 AND km.thoi_gian <= :end_month_2)', 'tong_km_thang', array(':ma_kh_2' => $ma_kh, ':start_month_2' => $start_month, ':end_month_2' => $end_month));
    $result = $query->execute();
    $data = array();
    $tru_tien = 0;
    if ($loai_kh) {
      $tru_tien = variable_get('tru_tien_khach_hang_dac_biet', 500);
    }
    while ($khuyen_mai = $result->fetchObject()) {
      if ($khuyen_mai->idloai == 1) {
        $khuyen_mai->gia_tien = $khuyen_mai->gia_tien - $tru_tien;
      }
      $data[$ref_arr[$khuyen_mai->ma_thanh_pham]][0] = $khuyen_mai->gia_tien;
      $data[$ref_arr[$khuyen_mai->ma_thanh_pham]][1] = $khuyen_mai->so_luong;
      $data[$ref_arr[$khuyen_mai->ma_thanh_pham]][2] = $khuyen_mai->tong_mua_thang ? $khuyen_mai->tong_mua_thang : 0;
      $data[$ref_arr[$khuyen_mai->ma_thanh_pham]][3] = $khuyen_mai->tong_km_thang ? $khuyen_mai->tong_km_thang : 0;
    }
    return $data;
  }

  function insert_phieu_ban_hang($ma_khach_hang, $ghi_chu, $thoi_gian) {
    global $user;
    $query = db_insert('dt_ban_hang');
    $arr = array(
      'uid' => $user->uid,
      'ma_khach_hang' => $ma_khach_hang,
      'ma_phieu_ban_hang' => '',
      'trang_thai' => 0,
      'trang_thai_bao_hanh_truoc' => 0,
      'tong_tien' => 0,
      'thuc_thu' => 0,
      'tong_tien_tru' => 0,
      'tien_no' => 0,
      'tien_bot' => 0,
      'ghi_chu' => $ghi_chu,
      'thoi_gian' => $thoi_gian
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $id = $query->execute();
    return $id;
  }

  function insert_hang_mua(&$tong_so_xuat_kho, $ma_phieu_ban_hang, $thong_tin_san_pham_arr, $ma_hang_mua_arr, $so_luong_hang_mua_arr, $thoi_gian) {
    global $user;
    $i = 0;
    $query = db_insert('dt_ban_hang_chi_tiet_hang_mua');
    $tong_tien = 0;
    foreach ($ma_hang_mua_arr as $key => $ma_hang_mua) {
      if (isset($tong_so_xuat_kho[$ma_hang_mua])) {
        $tong_so_xuat_kho[$ma_hang_mua] += $so_luong_hang_mua_arr[$key];
      }
      else {
        $tong_so_xuat_kho[$ma_hang_mua] = $so_luong_hang_mua_arr[$key];
      }
      $tong_tien += $so_luong_hang_mua_arr[$key] * $thong_tin_san_pham_arr[$ma_hang_mua]->gia_tien;
      $arr = array(
        'uid' => $user->uid,
        'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
        'ma_thanh_pham' => $ma_hang_mua,
        'so_luong' => $so_luong_hang_mua_arr[$key],
        'gia_tien' => $thong_tin_san_pham_arr[$ma_hang_mua]->gia_tien,
        'thoi_gian' => $thoi_gian
      );
      if (!$i) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      $i++;
    }
    $query->execute();
    return $tong_tien;
  }

  function insert_hang_hkm(&$tong_so_xuat_kho, $ma_phieu_ban_hang, $thong_tin_san_pham_arr, $ma_hang_mua_arr, $so_luong_khuyen_mai_arr, $tru_tien_khuyen_mai_arr, $thoi_gian) {
    global $user;
    $i = 0;
    $query = db_insert('dt_ban_hang_chi_tiet_khuyen_mai');
    $tong_tien = 0;
    foreach ($ma_hang_mua_arr as $key => $ma_hang_mua) {
      $thanh_pham = get_san_pham_theo_ma($ma_hang_mua);
      //$don_gia = $thanh_pham->gia_tien;
      if ($so_luong_khuyen_mai_arr[$key]) {
        if (isset($tru_tien_khuyen_mai_arr[$key])) {
          $gia_tien = $thong_tin_san_pham_arr[$ma_hang_mua]->gia_tien;
          $tong_tien += $so_luong_khuyen_mai_arr[$key] * $gia_tien;
          $don_gia = $gia_tien;
        }
        else {
          $gia_tien = 0;
          $don_gia = $thanh_pham->gia_tien;
          if (isset($tong_so_xuat_kho[$ma_hang_mua])) {
            $tong_so_xuat_kho[$ma_hang_mua] += $so_luong_khuyen_mai_arr[$key];
          }
          else {
            $tong_so_xuat_kho[$ma_hang_mua] = $so_luong_khuyen_mai_arr[$key];
          }
        }
        $arr = array(
          'uid' => $user->uid,
          'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
          'ma_thanh_pham' => $ma_hang_mua,
          'type' => 'km_mua_sp_thang',
          'so_luong' => $so_luong_khuyen_mai_arr[$key],
          'tru_tien' => $gia_tien,
          'gia_tien' => $don_gia,
          'thoi_gian' => $thoi_gian
        );
        if (!$i) {
          $query->fields(array_keys($arr));
        }
        $query->values($arr);
        $i++;
      }
    }
    if ($i) {
      $query->execute();
    }
    return $tong_tien;
  }

  function insert_hang_dbh(&$tong_so_xuat_kho, $ma_phieu_ban_hang, $tra_hang_duoc_bao_hanh_arr, $ma_hang_duoc_bao_hanh_arr, $mp_hang_duoc_bao_hanh_arr, $so_luong_hang_duoc_bao_hanh_arr, $tru_tien_hang_duoc_bao_hanh_arr, $don_gia_tru_tien_hang_duoc_bao_hanh_arr, $thoi_gian) {
    global $user;
    $i = 0;
    $query = db_insert('dt_ban_hang_chi_tiet_duoc_bao_hanh');
    $tong_tien = 0;
    foreach ($ma_hang_duoc_bao_hanh_arr as $id_hang_duoc_bao_hanh => $ma_hang) {
      if (in_array($mp_hang_duoc_bao_hanh_arr[$id_hang_duoc_bao_hanh], $tra_hang_duoc_bao_hanh_arr)) {
        if (isset($tru_tien_hang_duoc_bao_hanh_arr[$id_hang_duoc_bao_hanh])) {
          $gia_tien = str_replace(',', '', $don_gia_tru_tien_hang_duoc_bao_hanh_arr[$id_hang_duoc_bao_hanh]);
          $tong_tien += $so_luong_hang_duoc_bao_hanh_arr[$id_hang_duoc_bao_hanh] * $gia_tien;
          db_update('dt_san_pham_hoi_chi_tiet')
            ->fields(array(
                          'don_gia_tru_tien' => $gia_tien,
                          'thoi_gian_tra_bh' => $thoi_gian
                     ))
            ->condition('id', $id_hang_duoc_bao_hanh)
            ->execute();
        }
        else {
          if (isset($tong_so_xuat_kho[$ma_hang])) {
            $tong_so_xuat_kho[$ma_hang] += $so_luong_hang_duoc_bao_hanh_arr[$id_hang_duoc_bao_hanh];
          }
          else {
            $tong_so_xuat_kho[$ma_hang] = $so_luong_hang_duoc_bao_hanh_arr[$id_hang_duoc_bao_hanh];
          }
        }
        $arr = array(
          'uid' => $user->uid,
          'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
          'id_chi_tiet_hoi' => $id_hang_duoc_bao_hanh,
          'thoi_gian' => $thoi_gian
        );
        if (!$i) {
          $query->fields(array_keys($arr));
        }
        $query->values($arr);
        $i++;
      }
    }
    if ($i) {
      $query->execute();
      db_update('dt_san_pham_hoi')
        ->fields(array(
                      'trang_thai_bao_hanh' => 1,
                 ))
        ->condition('ma_phieu_hang_hoi', $tra_hang_duoc_bao_hanh_arr, 'in')
        ->execute();
    }
    return $tong_tien;
  }

  function insert_hang_hnc(&$tong_so_xuat_kho, $ma_phieu_ban_hang, $so_luong_hang_no_cu_arr, $tru_tien_hang_no_cu_arr, $don_gia_tru_tien_hang_no_cu_arr, $thoi_gian) {
    global $user;
    $i = 0;
    $query = db_insert('dt_ban_hang_chi_tiet_hang_no');
    $tong_tien = 0;
    foreach ($so_luong_hang_no_cu_arr as $ma_hang => $so_luong_hang_no_cu) {
      if (isset($tru_tien_hang_no_cu_arr[$ma_hang])) {
        $gia_tien = str_replace(',', '', $don_gia_tru_tien_hang_no_cu_arr[$ma_hang]);
        $tong_tien += $so_luong_hang_no_cu * $gia_tien;
      }
      else {
        $gia_tien = 0;
        if (isset($tong_so_xuat_kho[$ma_hang])) {
          $tong_so_xuat_kho[$ma_hang] += $so_luong_hang_no_cu;
        }
        else {
          $tong_so_xuat_kho[$ma_hang] = $so_luong_hang_no_cu;
        }
      }
      $arr = array(
        'uid' => $user->uid,
        'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
        'ma_thanh_pham' => $ma_hang,
        'so_luong' => $so_luong_hang_no_cu,
        'tru_tien' => $gia_tien,
        'type' => 0,
        'thoi_gian' => $thoi_gian
      );
      if (!$i) {
        $query->fields(array_keys($arr));
      }
      $query->values($arr);
      $i++;
    }
    if ($i) {
      $query->execute();
    }
    return $tong_tien;
  }

  function insert_hang_no_bht(&$tong_so_xuat_kho, $ma_phieu_ban_hang, $so_luong_hang_dang_no_arr, $ma_hang_no_bht_arr, $thoi_gian) {
    global $user;
    $i = 0;
    $query = db_insert('dt_ban_hang_tra_no_bht');
    foreach ($so_luong_hang_dang_no_arr as $id => $so_luong_hang_dang_no) {
      if (in_array($ma_hang_no_bht_arr[$id], array_keys($tong_so_xuat_kho))) {
        if ($so_luong_hang_dang_no >= $tong_so_xuat_kho[$ma_hang_no_bht_arr[$id]]) {
          unset($tong_so_xuat_kho[$ma_hang_no_bht_arr[$id]]);
          $tra_no = $tong_so_xuat_kho[$ma_hang_no_bht_arr[$id]];
        }
        else {
          $tong_so_xuat_kho[$ma_hang_no_bht_arr[$id]] -= $so_luong_hang_dang_no;
          $tra_no = $so_luong_hang_dang_no;
        }
        update_so_luong_tra_no_bht('+', $tra_no, $id);
        $arr = array(
          'uid' => $user->uid,
          'id_bao_hanh_truoc' => $id,
          'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
          'ma_thanh_pham' => $ma_hang_no_bht_arr[$id],
          'so_luong' => $tra_no,
          'thoi_gian' => $thoi_gian
        );
        if (!$i) {
          $query->fields(array_keys($arr));
        }
        $query->values($arr);
        $i++;
      }
    }
    if ($i) {
      $query->execute();
    }
  }

  function update_so_luong_tra_no_bht($sign, $so_luong, $id_bht) {
    db_update('dt_ban_hang_chi_tiet_bao_hanh_truoc')
      ->expression('tra_no', 'tra_no ' . $sign . ' :so_luong', array(':so_luong' => $so_luong))
      ->condition('id', $id_bht)
      ->execute();
  }

  function insert_hang_bht(&$tong_so_xuat_kho, $ma_phieu_ban_hang, $ma_hang_bao_hanh_truoc_arr, $so_luong_hang_bao_hanh_truoc_arr, $thoi_gian) {
    global $user;
    $i = 0;
    $query = db_insert('dt_ban_hang_chi_tiet_bao_hanh_truoc');
    foreach ($ma_hang_bao_hanh_truoc_arr as $key => $ma_hang) {
      if (isset($tong_so_xuat_kho[$ma_hang])) {
        $tong_so_xuat_kho[$ma_hang] += $so_luong_hang_bao_hanh_truoc_arr[$key];
      }
      else {
        $tong_so_xuat_kho[$ma_hang] = $so_luong_hang_bao_hanh_truoc_arr[$key];
      }
      $arr = array(
        'uid' => $user->uid,
        'ma_phieu_hang_hoi' => '',
        'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
        'ma_thanh_pham' => $ma_hang,
        'so_luong' => $so_luong_hang_bao_hanh_truoc_arr[$key],
        'nhan_lai' => 0,
        'thoi_gian' => $thoi_gian
      );
      if (!$i) {
        $query->fields(array_keys($arr));
        $i++;
      }
      $query->values($arr);
    }
    if ($i) {
      $query->execute();
    }
    return $i;
  }

  function update_phieu_ban_hang($ma_phieu_ban_hang, $trang_thai_bht, $tong_tien_mua, $tong_tien_tru, $id_phieu_ban_hang) {
    db_update('dt_ban_hang')
      ->fields(array(
                    'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
                    'trang_thai_bao_hanh_truoc' => $trang_thai_bht,
                    'tong_tien' => $tong_tien_mua,
                    'thuc_thu' => $tong_tien_mua - $tong_tien_tru,
                    'tong_tien_tru' => $tong_tien_tru,
                    // 'tien_bot'=>
               ))
      ->condition('id', $id_phieu_ban_hang)
      ->execute();
  }

  function update_tien_no_ban_hang($tien_no, $ma_khach_hang) {
    db_update('dt_khach_hang')
      ->fields(array(
                    'tien_no' => $tien_no,
               ))
      ->condition('ma_khach_hang', $ma_khach_hang)
      ->execute();
  }

  function delete_hn($ma_khach_hang, $ma_san_pham = array()) {
    $query = db_delete('dt_hang_no');
    $query->condition('ma_khach_hang', $ma_khach_hang);
    if (!empty($ma_san_pham)) {
      $query->condition('ma_thanh_pham', $ma_san_pham, 'in');
    }
    $query->execute();
  }

  function update_so_luong_san_pham_ban_hang($ma_phieu_ban_hang, $ma_khach_hang, $tong_so_xuat_kho, $thong_tin_san_pham_arr, $thoi_gian) {
    global $user;
    $i = 0;
    $i_sl = 0;
    $query = db_insert('dt_hang_no');
    $query_sl = db_insert('dt_ban_hang_chi_tiet_so_luong');
    foreach ($tong_so_xuat_kho as $ma_hang => $so_luong) {
      if ($so_luong > $thong_tin_san_pham_arr[$ma_hang]->so_luong) {
        update_so_luong_san_pham('-', $thong_tin_san_pham_arr[$ma_hang]->so_luong, $ma_hang);
        $arr = array(
          'uid' => $user->uid,
          'ma_khach_hang' => $ma_khach_hang,
          'ma_thanh_pham' => $ma_hang,
          'so_luong' => $so_luong - $thong_tin_san_pham_arr[$ma_hang]->so_luong,
          'thoi_gian' => $thoi_gian
        );
        if (!$i) {
          $query->fields(array_keys($arr));
        }
        $query->values($arr);
        $i++;
      }
      else {
        update_so_luong_san_pham('-', $so_luong, $ma_hang);
      }
      //      if ($thong_tin_san_pham_arr[$ma_hang]->so_luong) {
      if ($so_luong > $thong_tin_san_pham_arr[$ma_hang]->so_luong) {
        $arr_sl = array(
          'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
          'ma_thanh_pham' => $ma_hang,
          'so_luong' => $so_luong,
          'xuat_kho' => $thong_tin_san_pham_arr[$ma_hang]->so_luong,
        );
      }
      else {
        $arr_sl = array(
          'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
          'ma_thanh_pham' => $ma_hang,
          'so_luong' => $so_luong,
          'xuat_kho' => $so_luong,
        );
      }
      if (!$i_sl) {
        $query_sl->fields(array_keys($arr_sl));
      }
      $query_sl->values($arr_sl);
      $i_sl++;
      //      }
    }
    if ($i) {
      $query->execute();
    }
    if ($i_sl) {
      $query_sl->execute();
    }
  }

  function get_thong_tin_san_pham_ban_hang($loai_khach_hang, $ma_san_pham_arr) {
    $query = db_select('dt_san_pham', 'sp');
    $query->fields('sp', array('ma_thanh_pham', 'ten_thanh_pham', 'gia_tien', 'so_luong', 'idloai'));
    $query->condition('sp.ma_thanh_pham', $ma_san_pham_arr, 'in');
    $result = $query->execute();
    $data = array();
    $tru_tien = variable_get('tru_tien_khach_hang_dac_biet', 500);
    while ($rs = $result->fetchObject()) {
      if ($loai_khach_hang) {
        if ($rs->idloai == 1) {
          $rs->gia_tien = $rs->gia_tien - $tru_tien;
        }
      }
      $data[$rs->ma_thanh_pham] = $rs;
    }
    return $data;
  }

  function get_phieu_ban_hang_theo_ma($ma_phieu_ban_hang) {
    $query = db_select('dt_ban_hang', 'bh');
    $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
    $query->fields('bh', array('ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'tien_no', 'tien_bot', 'thoi_gian'));
    $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai'));
    $query->addField('kh', 'tien_no', 'tien_no_ton');
    $query->condition('bh.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    return $query->execute()->fetchObject();
  }

  function load_hang_no_moi($ma_khach_hang) {
    $query = db_select('dt_hang_no', 'hn');
    $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = hn.ma_khach_hang');
    $query->innerJoin('dt_san_pham', 'sp', 'hn.ma_thanh_pham = sp.ma_thanh_pham');
    $query->fields('kh', array('loai'));
    $query->fields('hn', array('id', 'ma_thanh_pham', 'so_luong'));
    $query->fields('sp', array('ten_thanh_pham', 'gia_tien', 'idloai'));
    $query->condition('hn.ma_khach_hang', $ma_khach_hang);
    $result = $query->execute();
    $rows_hnc = array();
    $tru_tien = variable_get('tru_tien_khach_hang_dac_biet', 500);
    while ($hang_no_moi = $result->fetchObject()) {
      if ($hang_no_moi->loai) {
        if ($hang_no_moi->idloai == 1) {
          $hang_no_moi->gia_tien = $hang_no_moi->gia_tien - $tru_tien;
        }
      }
      $rows_hnc[] = array(
        '<label class="data-text ten-hang-no-moi">' . $hang_no_moi->ten_thanh_pham . ' [' . $hang_no_moi->ma_thanh_pham . ']' . '</label>',
        '<label class="data-text so-luong-hang-no-moi">' . $hang_no_moi->so_luong . '</label>',
//        '<label class="data-text lbl-don-gia-hang-no-moi">' . number_format($hang_no_moi->gia_tien) . '</label>',
//        '<input type="checkbox" ' . ((isset($_POST['tru_tien_hang_no_moi']) && isset($_POST['tru_tien_hang_no_moi'][$hang_no_moi->ma_thanh_pham]))
//          ? 'checked="checked"'
//          : '') . ' name="tru_tien_hang_no_moi[' . $hang_no_moi->ma_thanh_pham . ']" class="form-checkbox checkbox txt-tru-tien-hang-no-moi tru-tien" value="' . $hang_no_moi->ma_thanh_pham . '" />
//        <input type="text" name="don_gia_tru_tien_hang_no_moi[' . $hang_no_moi->ma_thanh_pham . ']" class="form-text text txt-don-gia-tru-tien-hang-no-moi format-currency ' . (($_POST && $_POST['error']['key'] == $hang_no_moi->ma_thanh_pham && isset($_POST['error']['don_gia_tru_tien_hang_no_moi']))
//          ? 'error' : '') . '" autocomplete="OFF" value="' . ($_POST
//          ? $_POST['don_gia_tru_tien_hang_no_moi'][$hang_no_moi->ma_thanh_pham] : '') . '" />
//        <input type="hidden" name="so_luong_hang_no_moi[' . $hang_no_moi->ma_thanh_pham . ']" value="' . $hang_no_moi->so_luong . '" />'
      );
    }
    return $rows_hnc;
  }

  function get_danh_sach_phieu_ban_hang($filter, $header = array()) {
    $query = db_select('dt_ban_hang', 'bh')->extend('PagerDefault')->extend('TableSort');
    ;
    $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
    $query->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
    $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
    if ($filter['ma_ref']) {
      $query->condition('kh.ma_khach_hang', $filter['ma_ref']);
    }
    $query->orderByHeader($header);
    $result = $query->limit(20)->execute();
    $data = array();
    $option_tt = get_option_trang_thai_ban_hang();
    $option_bht = get_option_trang_thai_bao_hanh_truoc();
    $admin_perm = user_access('admin xoa sp');
    while ($rs = $result->fetchObject()) {
      $output = '<a href="  ../san_pham/phieu_xuat_nhap/xoa_phieu/banhang/' . $rs->ma_phieu_ban_hang . '" onclick="return check_xoa_phieu();">Xóa</a>';
      $data[] = array(
        $rs->ma_phieu_ban_hang,
        $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
        number_format($rs->tong_tien),
        number_format($rs->tong_tien_tru),
        number_format($rs->tong_tien - $rs->tong_tien_tru - $rs->thuc_thu),
        number_format($rs->thuc_thu),
        $option_bht[$rs->trang_thai_bao_hanh_truoc],
        date('H:i:s d/m/Y', $rs->thoi_gian),
        ($admin_perm ? ($output . ' | ')
          : '') . l('Chi tiết', 'san_pham/ban_hang/in_phieu_ban_hang/ban_hang/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
      );
    }
    return $data;
  }

  function insert_hang_hnm($ma_phieu_ban_hang, $so_luong_hang_no_moi_arr, $tru_tien_hang_no_moi_arr, $don_gia_tru_tien_hang_no_moi_arr, $thoi_gian) {
    global $user;
    $i = 0;
    $query = db_insert('dt_ban_hang_chi_tiet_hang_no');
    $tong_tien = 0;
    foreach ($so_luong_hang_no_moi_arr as $ma_hang => $so_luong_hang_no_moi) {
      if (isset($tru_tien_hang_no_moi_arr[$ma_hang])) {
        $gia_tien = str_replace(',', '', $don_gia_tru_tien_hang_no_moi_arr[$ma_hang]);
        $tong_tien += $so_luong_hang_no_moi * $gia_tien;
        $arr = array(
          'uid' => $user->uid,
          'ma_phieu_ban_hang' => $ma_phieu_ban_hang,
          'ma_thanh_pham' => $ma_hang,
          'so_luong' => $so_luong_hang_no_moi,
          'tru_tien' => $gia_tien,
          'type' => 1,
          'thoi_gian' => $thoi_gian
        );
        if (!$i) {
          $query->fields(array_keys($arr));
        }
        $query->values($arr);
        $i++;
      }
    }
    if ($i) {
      $query->execute();
    }
    return $tong_tien;
  }

  function update_phieu_thuc_thu($tong_tien, $thuc_thu, $tong_tien_tru, $ma_phieu_ban_hang, $tien_no) {
    db_update('dt_ban_hang')
      ->fields(array(
                    'trang_thai' => 1,
                    'thuc_thu' => $thuc_thu,
                    'tong_tien_tru' => $tong_tien_tru,
                    //Lưu tổng số tiền phải thanh toan của phiếu.Gồm cả công nợ.Để thay cho dt_khach_hang.tien_no
                    'tien_no' => $tien_no,
                    'tien_bot' => $tong_tien - $tong_tien_tru - $thuc_thu
               ))
      ->condition('ma_phieu_ban_hang', $ma_phieu_ban_hang)
      ->execute();
  }

  function update_trang_thai_phieu_san_pham($data, $type, $ma_phieu) {
    switch ($type) {
      case 'hoi':
        db_update('dt_san_pham_hoi')
          ->fields(array('trang_thai' => 1, 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_hang_hoi', $ma_phieu)
          ->execute();
        break;
      case 'nguyen':
        db_update('dt_san_pham_nguyen')
          ->fields(array('trang_thai' => 1, 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_hang_nguyen', $ma_phieu)
          ->execute();
        break;
    }
  }

  function update_phieu_thanh_pham_chi_tiet($type, $id, $ma_vat_tu, $so_luong, $so_luong_hoi = 0) {
    $time = time();
    switch ($type) {
      case 'nguyen':
        db_update('dt_san_pham_nguyen_chi_tiet')
          ->fields(array('ma_thanh_pham' => $ma_vat_tu, 'so_luong' => $so_luong, 'thoi_gian' => $time))
          ->condition('id', $id)
          ->execute();
        break;
      case 'hoi':
        db_update('dt_san_pham_hoi_chi_tiet')
          ->fields(array('ma_thanh_pham' => $ma_vat_tu, 'so_luong' => $so_luong, 'so_luong_hoi' => $so_luong_hoi, 'thoi_gian' => $time))
          ->condition('id', $id)
          ->execute();
        break;
    }
  }

  function get_phieu_chi_tiet_san_pham_by_id($type, $id) {
    switch ($type) {
      case 'nguyen':
        $query = db_select('dt_san_pham_nguyen', 'p');
        $query->innerJoin('dt_san_pham_nguyen_chi_tiet', 'ref', 'ref.ma_phieu_hang_nguyen = p.ma_phieu_hang_nguyen');
        $query->fields('p', array('trang_thai'));
        $query->fields('ref', array('id', 'ma_thanh_pham', 'so_luong'));
        $query->addField('p', 'ma_phieu_hang_nguyen', 'ma_phieu');
        $query->condition('ref.id', $id);
        break;
      case 'hoi':
        $query = db_select('dt_san_pham_hoi', 'p');
        $query->innerJoin('dt_san_pham_hoi_chi_tiet', 'ref', 'ref.ma_phieu_hang_hoi = p.ma_phieu_hang_hoi');
        $query->fields('p', array('trang_thai'));
        $query->fields('ref', array('id', 'ma_thanh_pham', 'so_luong', 'so_luong_hoi'));
        $query->addField('p', 'ma_phieu_hang_hoi', 'ma_phieu');
        $query->condition('ref.id', $id);
        break;
    }
    $result = $query->execute()->fetchObject();
    return $result;
  }

  function insert_lich_su_phieu_san_pham_chi_tiet($type, $ma_chi_tiet, $ma_phieu, $ma_vat_tu, $so_luong, $so_luong_hoi = 0) {
    $query = db_insert('dt_san_pham_lich_su_phieu');
    $arr = array(
      'ma_phieu_chi_tiet' => $ma_chi_tiet,
      'ma_phieu' => $ma_phieu,
      'ma_san_pham' => $ma_vat_tu,
      'so_luong' => $so_luong,
      'so_luong_hoi' => $so_luong_hoi,
      'type' => $type,
      'thoi_gian' => time()
    );
    $query->fields(array_keys($arr));
    $query->values($arr);
    $query->execute();
    return $ma_chi_tiet;
  }

  function update_gia_tien_phieu_san_pham($data, $type, $ma_phieu) {
    switch ($type) {
      case 'nguyen':
        db_update('dt_san_pham_nguyen')
          ->fields(array('tong_tien' => $data['tong_tien'], 'ghi_chu' => $data['ghi_chu']))
          ->condition('ma_phieu_hang_nguyen', $ma_phieu)
          ->execute();
        foreach ($data['gia_tien'] as $key => $gia_tien) {
          $gia_tien = str_replace(',', '', $gia_tien);
          db_update('dt_san_pham_nguyen_chi_tiet')
            ->fields(array('gia_tien' => $gia_tien))
            ->condition('id', $data['id'][$key])
            ->execute();
        }
        break;
    }
  }

  function get_danh_sach_xuat_thanh_pham($header) {
    $query = db_select('dt_ban_hang', 'bh')->extend('PagerDefault')->extend('TableSort');
    $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
    $query->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
    $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
    $query->condition('bh.trang_thai', 1);
    $query->orderByHeader($header);
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        $rs->ma_phieu_ban_hang,
        $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
        date('H:i:s d/m/Y', $rs->thoi_gian),
        l('Xuất hàng', 'san_pham/nhap_xuat/xuat_thanh_pham/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
      );
    }
    return $data;
  }

  function get_danh_sach_nhap_hoi_bht($header) {
    $query = db_select('dt_ban_hang', 'bh')->extend('PagerDefault')->extend('TableSort');
    $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
    $query->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
    $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
    $query->condition('bh.trang_thai_bao_hanh_truoc', 1);
    $query->orderByHeader($header);
    $result = $query->limit(20)->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = array(
        $rs->ma_phieu_ban_hang,
        $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
        date('H:i:s d/m/Y', $rs->thoi_gian),
        l('Nhập hồi', 'san_pham/nhap_xuat/nhap_hoi_bht/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
      );
    }
    return $data;
  }

  function get_chi_tiet_hoi_bht($header, $ma_phieu_ban_hang) {
    $query = db_select('dt_ban_hang_chi_tiet_bao_hanh_truoc', 'ct')->extend('TableSort');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_thanh_pham', 'so_luong', 'nhan_lai'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->condition('ct.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $query->orderByHeader($header);
    $result = $query->execute();
    $i = 0;
    $data = array();
    while ($rs = $result->fetchObject()) {
      $i++;
      $data[] = array(
        $i,
        $rs->ma_thanh_pham,
        $rs->ten_thanh_pham,
        $rs->so_luong . '<input type="hidden" name="so_luong[' . $rs->ma_thanh_pham . ']" value="' . $rs->so_luong . '" />',
        '<input type="text" name="nhan_lai[' . $rs->ma_thanh_pham . ']" class="form-text text txt-nhan-lai ' . (($_POST && $_POST['error']['key'] == $rs->ma_thanh_pham && isset($_POST['error']['nhan_lai']))
          ? 'error' : '') . '" autocomplete="OFF" value="' . ($_POST
          ? $_POST['nhan_lai'][$rs->ma_thanh_pham] : '') . '" />',
        '<input type="text" name="so_no[' . $rs->ma_thanh_pham . ']" class="form-text text txt-so-no ' . (($_POST && $_POST['error']['key'] == $rs->ma_thanh_pham && isset($_POST['error']['so_no']))
          ? 'error' : '') . '" autocomplete="OFF" value="' . ($_POST
          ? $_POST['so_no'][$rs->ma_thanh_pham] : '') . '" />'
      );
    }
    return $data;
  }

  function update_trang_thai_tra_hang($ma_phieu_ban_hang) {
    db_update('dt_ban_hang')
      ->fields(array(
                    'trang_thai' => 2,
               ))
      ->condition('ma_phieu_ban_hang', $ma_phieu_ban_hang)
      ->execute();
  }

  function insert_hoi_bht($phieu_ban_hang, $so_luong_arr, $nhan_lai_arr, $so_no_arr, $ghi_chu) {
    global $user;
    $thoi_gian = time();
    $ma_phieu_hoi = insert_san_pham_hang_hoi($user->uid, $phieu_ban_hang->ma_khach_hang, $ghi_chu, $thoi_gian, 1);
    update_ma_phieu_hang_hoi_bht($ma_phieu_hoi, $phieu_ban_hang->ma_phieu_ban_hang);
    $query = db_insert('dt_san_pham_hoi_chi_tiet');
    $query->fields(array('uid', 'ma_thanh_pham', 'ma_phieu_hang_hoi', 'so_luong', 'so_luong_hoi', 'don_gia_tru_tien', 'thoi_gian'));
    foreach ($so_luong_arr as $ma_thanh_pham => $so_luong) {
      $so_luong_dbh = $so_luong;
      if ($nhan_lai_arr[$ma_thanh_pham] || $so_no_arr[$ma_thanh_pham]) {
        $nhan_lai = $nhan_lai_arr[$ma_thanh_pham];
        $so_no = $so_no_arr[$ma_thanh_pham];
        if ($nhan_lai) {
          update_so_luong_san_pham('+', $nhan_lai, $ma_thanh_pham);
        }
        else {
          $nhan_lai = 0;
        }
        if (!$so_no) {
          $so_no = 0;
        }
        $so_luong = $so_luong - $nhan_lai;
        $so_luong_dbh = $so_luong - $so_no;
        update_nhan_lai_bht($nhan_lai, $so_no, $phieu_ban_hang->ma_phieu_ban_hang, $ma_thanh_pham);
      }
      $arr = array(
        'uid' => $user->uid,
        'ma_thanh_pham' => $ma_thanh_pham,
        'ma_phieu_hang_hoi' => $ma_phieu_hoi,
        'so_luong' => $so_luong_dbh,
        'so_luong_hoi' => $so_luong,
        'don_gia_tru_tien' => 0,
        'thoi_gian' => $thoi_gian
      );
      $query->values($arr);
      update_so_luong_san_pham_hong('+', $so_luong_dbh, $ma_thanh_pham);
    }
    $query->execute();
  }

  function update_trang_thai_hoi_bht($ma_phieu_ban_hang) {
    db_update('dt_ban_hang')
      ->fields(array(
                    'trang_thai_bao_hanh_truoc' => 2,
               ))
      ->condition('ma_phieu_ban_hang', $ma_phieu_ban_hang)
      ->execute();
  }

  function update_nhan_lai_bht($nhan_lai, $so_no, $ma_phieu_ban_hang, $ma_thanh_pham) {
    db_update('dt_ban_hang_chi_tiet_bao_hanh_truoc')
      ->fields(array(
                    'nhan_lai' => $nhan_lai,
                    'so_no' => $so_no,
               ))
      ->condition('ma_phieu_ban_hang', $ma_phieu_ban_hang)
      ->condition('ma_thanh_pham', $ma_thanh_pham)
      ->execute();
  }

  function update_ma_phieu_hang_hoi_bht($ma_phieu_hoi, $ma_phieu_ban_hang) {
    db_update('dt_ban_hang_chi_tiet_bao_hanh_truoc')
      ->fields(array(
                    'ma_phieu_hang_hoi' => $ma_phieu_hoi,
               ))
      ->condition('ma_phieu_ban_hang', $ma_phieu_ban_hang)
      ->execute();
  }

  function get_ban_hang_so_luong($ma_phieu_ban_hang) {
    $data = array();
    $data['header'] = array(
      'STT',
      'Mã sản phẩm',
      'Tên sản phẩm',
      'Số lượng'
    );
    $query = db_select('dt_ban_hang_chi_tiet_so_luong', 'ct');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_thanh_pham', 'so_luong', 'xuat_kho'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->condition('ct.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $result = $query->execute();
    $i = 0;
    $data['rows'] = array();
    while ($rs = $result->fetchObject()) {
      if ($rs->xuat_kho) {
        $i++;
        $data['rows'][] = array(
          $i,
          $rs->ma_thanh_pham,
          $rs->ten_thanh_pham,
          $rs->xuat_kho,
        );
      }
    }
    return $data;
  }

  function get_ban_hang_so_luong_lst($ma_phieu_ban_hang) {
    $data = array();
    $query = db_select('dt_ban_hang_chi_tiet_so_luong', 'ct');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_thanh_pham', 'so_luong', 'xuat_kho'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->condition('ct.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $result = $query->execute();
    while ($rs = $result->fetchObject()) {
      $data[] = $rs;
    }
    return $data;
  }

  function get_ban_hang_duoc_bao_hang_lst($ma_phieu_ban_hang) {
    $query = db_select('dt_ban_hang_chi_tiet_duoc_bao_hanh', 'p');
    $query->innerJoin('dt_san_pham_hoi_chi_tiet', 'ct', 'p.id_chi_tiet_hoi = ct.id');
    $query->fields('ct', array('id', 'ma_phieu_hang_hoi', 'ma_thanh_pham', 'so_luong', 'so_luong_hoi', 'don_gia_tru_tien'));
    $query->condition('p.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $result = $query->execute();
    while ($rs = $result->fetchObject()) {
      $data[] = $rs;
    }
    return $data;
  }

  function get_ban_hang_hang_mua($ma_phieu_ban_hang) {
    $data = array();
    $data[] = array(
      'data' => array(
        array('data' => 'Danh sách hàng mua', 'colspan' => 6, 'class' => array('text-center')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => 'STT', 'width' => '5%'),
        array('data' => 'Mã sản phẩm', 'width' => '15%'),
        array('data' => 'Tên sản phẩm', 'width' => '35%'),
        array('data' => 'Số lượng', 'width' => '15%'),
        array('data' => 'Giá tiền', 'width' => '15%'),
        array('data' => 'Thành tiền', 'width' => '15%'),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $query = db_select('dt_ban_hang_chi_tiet_hang_mua', 'ct');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_thanh_pham', 'so_luong', 'gia_tien'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->condition('ct.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $result = $query->execute();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
      $data[] = array(
        $i,
        $rs->ma_thanh_pham,
        $rs->ten_thanh_pham,
        $rs->so_luong,
        number_format($rs->gia_tien),
        number_format($rs->so_luong * $rs->gia_tien),
      );
    }
    if (!$i) {
      return array();
    }
    return $data;
  }

  function get_ban_hang_hang_bao_hanh_truoc($ma_phieu_ban_hang) {
    $data = array();
    $data[] = array(
      'data' => array(
        array('data' => 'Danh sách hàng bảo hành trước', 'colspan' => 6, 'class' => array('text-center')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => 'STT', 'width' => '5%'),
        array('data' => 'Mã sản phẩm', 'width' => '15%'),
        array('data' => 'Tên sản phẩm', 'width' => '35%'),
        array('data' => 'Số lượng', 'width' => '15%'),
        array('data' => 'Nhận lại', 'width' => '15%'),
        array('data' => 'Khách hàng nợ', 'width' => '15%'),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $query = db_select('dt_ban_hang_chi_tiet_bao_hanh_truoc', 'ct');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_thanh_pham', 'so_luong', 'nhan_lai', 'so_no'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->condition('ct.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $result = $query->execute();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
      $data[] = array(
        $i,
        $rs->ma_thanh_pham,
        $rs->ten_thanh_pham,
        $rs->so_luong,
        $rs->nhan_lai,
        $rs->so_no,
      );
    }
    if (!$i) {
      return array();
    }
    return $data;
  }

  function get_ban_hang_hang_khuyen_mai($ma_phieu_ban_hang) {
    $data = array();
    $data[] = array(
      'data' => array(
        array('data' => 'Danh sách hàng khuyến mại', 'colspan' => 6, 'class' => array('text-center')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => 'STT', 'width' => '5%'),
        array('data' => 'Mã sản phẩm', 'width' => '15%'),
        array('data' => 'Tên sản phẩm', 'width' => '35%'),
        array('data' => 'Số lượng', 'width' => '15%'),
        array('data' => 'Đơn giá trừ tiền', 'width' => '15%'),
        array('data' => 'Thành tiền', 'width' => '15%'),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $query = db_select('dt_ban_hang_chi_tiet_khuyen_mai', 'ct');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_thanh_pham', 'so_luong', 'tru_tien'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->condition('ct.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $result = $query->execute();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
      $data[] = array(
        $i,
        $rs->ma_thanh_pham,
        $rs->ten_thanh_pham,
        $rs->so_luong,
        $rs->tru_tien ? number_format($rs->tru_tien) : '',
        $rs->tru_tien ? number_format($rs->so_luong * $rs->tru_tien) : '',
      );
    }
    if (!$i) {
      return array();
    }
    return $data;
  }

  function get_ban_hang_hang_duoc_bao_hanh($ma_phieu_ban_hang) {
    $data = array();
    $data['ghi_chu'] = array();
    $data['rows'][] = array(
      'data' => array(
        array('data' => 'Danh sách hàng được bảo hành (đã nhập hồi)', 'colspan' => 7, 'class' => array('text-center')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data['rows'][] = array(
      'data' => array(
        array('data' => 'STT', 'width' => '5%'),
        array('data' => 'Mã phiếu hồi', 'width' => '15%'),
        array('data' => 'Tên sản phẩm', 'width' => '25%'),
        array('data' => 'Mang đến', 'width' => '10%'),
        array('data' => 'Được bảo hành', 'width' => '15%'),
        array('data' => 'Đơn giá trừ tiền', 'width' => '15%'),
        array('data' => 'Thành tiền', 'width' => '15%'),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $query = db_select('dt_ban_hang_chi_tiet_duoc_bao_hanh', 'p');
    $query->innerJoin('dt_san_pham_hoi_chi_tiet', 'ct', 'p.id_chi_tiet_hoi = ct.id');
    $query->innerJoin('dt_san_pham_hoi', 'ph', 'ph.ma_phieu_hang_hoi = ct.ma_phieu_hang_hoi');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_phieu_hang_hoi', 'ma_thanh_pham', 'so_luong', 'so_luong_hoi', 'don_gia_tru_tien'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->fields('ph', array('ghi_chu'));
    $query->condition('p.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $result = $query->execute();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
      $data['rows'][] = array(
        $i,
        $rs->ma_phieu_hang_hoi,
        $rs->ten_thanh_pham . ' [' . $rs->ma_thanh_pham . ']',
        $rs->so_luong_hoi,
        $rs->so_luong,
        $rs->don_gia_tru_tien ? number_format($rs->don_gia_tru_tien) : '',
        $rs->don_gia_tru_tien ? number_format($rs->so_luong * $rs->don_gia_tru_tien) : '',
      );
      if ($rs->ghi_chu) {
        $data['ghi_chu'][$rs->ma_phieu_hang_hoi] = $rs->ghi_chu;
      }
    }
    if (!$i) {
      return array();
    }
    return $data;
  }

  function get_ban_hang_hang_no_bht($ma_phieu_ban_hang) {
    $data = array();
    $data['ghi_chu'] = array();
    $data['rows'][] = array(
      'data' => array(
        array('data' => 'Danh sách hàng khách hàng nợ bảo hành trước', 'colspan' => 8, 'class' => array('text-center')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data['rows'][] = array(
      'data' => array(
        array('data' => 'STT', 'width' => '5%'),
        array('data' => 'Mã phiếu bán hàng', 'width' => '15%'),
        array('data' => 'Tên sản phẩm', 'width' => '25%'),
        array('data' => 'Mang đi', 'width' => '10%'),
        array('data' => 'Nhận lại', 'width' => '10%'),
        array('data' => 'Số nợ', 'width' => '10%'),
        array('data' => 'Trả lần này', 'width' => '15%'),
        array('data' => 'Đã trả', 'width' => '10%'),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $query = db_select('dt_ban_hang_tra_no_bht', 'p');
    $query->innerJoin('dt_ban_hang_chi_tiet_bao_hanh_truoc', 'ct', 'p.id_bao_hanh_truoc = ct.id');
    $query->innerJoin('dt_san_pham_hoi', 'ph', 'ph.ma_phieu_hang_hoi = ct.ma_phieu_hang_hoi');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_phieu_hang_hoi', 'ma_phieu_ban_hang', 'ma_thanh_pham', 'so_luong', 'nhan_lai', 'so_no', 'tra_no'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->fields('ph', array('ghi_chu'));
    $query->addField('p', 'so_luong', 'tra_lan_nay');
    $query->condition('p.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $result = $query->execute();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
      $data['rows'][] = array(
        $i,
        $rs->ma_phieu_ban_hang,
        $rs->ten_thanh_pham . ' [' . $rs->ma_thanh_pham . ']',
        $rs->so_luong,
        $rs->nhan_lai,
        $rs->so_no,
        $rs->tra_lan_nay,
        $rs->tra_no,
      );
      if ($rs->ghi_chu) {
        $data['ghi_chu'][$rs->ma_phieu_ban_hang] = $rs->ghi_chu;
      }
    }
    if (!$i) {
      return array();
    }
    return $data;
  }

  function get_ban_hang_hang_no_cu($ma_phieu_ban_hang) {
    $data = array();
    $data[] = array(
      'data' => array(
        array('data' => 'Danh sách hàng nợ cũ', 'colspan' => 6, 'class' => array('text-center')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => 'STT', 'width' => '5%'),
        array('data' => 'Mã sản phẩm', 'width' => '15%'),
        array('data' => 'Tên sản phẩm', 'width' => '35%'),
        array('data' => 'Số lượng', 'width' => '15%'),
        array('data' => 'Đơn giá trừ tiền', 'width' => '15%'),
        array('data' => 'Thành tiền', 'width' => '15%'),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $query = db_select('dt_ban_hang_chi_tiet_hang_no', 'ct');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_thanh_pham', 'so_luong', 'tru_tien'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->condition('ct.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $query->condition('ct.type', 0);
    $result = $query->execute();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $i++;
      $data[] = array(
        $i,
        $rs->ma_thanh_pham,
        $rs->ten_thanh_pham,
        $rs->so_luong,
        $rs->tru_tien ? number_format($rs->tru_tien) : '',
        $rs->tru_tien ? number_format($rs->so_luong * $rs->tru_tien) : '',
      );
    }
    if (!$i) {
      return array();
    }
    return $data;
  }

  function get_ban_hang_hang_no_moi($ma_phieu_ban_hang) {
    $data = array();
    $data[] = array(
      'data' => array(
        array('data' => 'Danh sách hàng nợ mới', 'colspan' => 6, 'class' => array('text-center')),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $data[] = array(
      'data' => array(
        array('data' => 'STT', 'width' => '5%'),
        array('data' => 'Mã sản phẩm', 'width' => '15%'),
        array('data' => 'Tên sản phẩm', 'width' => '35%'),
        array('data' => 'Số lượng', 'width' => '15%'),
        array('data' => 'Đơn giá trừ tiền', 'width' => '15%'),
        array('data' => 'Thành tiền', 'width' => '15%'),
      ),
      'class' => array('thong-ke-vat-tu-header')
    );
    $query = db_select('dt_ban_hang_chi_tiet_so_luong', 'ct');
    $query->leftJoin('dt_ban_hang_chi_tiet_hang_no', 'hn', 'hn.ma_thanh_pham = ct.ma_thanh_pham AND hn.ma_phieu_ban_hang = ct.ma_phieu_ban_hang AND hn.type = 1');
    $query->innerJoin('dt_san_pham', 'sp', 'sp.ma_thanh_pham = ct.ma_thanh_pham');
    $query->fields('ct', array('id', 'ma_thanh_pham', 'so_luong', 'xuat_kho'));
    $query->fields('hn', array('tru_tien'));
    $query->fields('sp', array('ten_thanh_pham'));
    $query->condition('ct.ma_phieu_ban_hang', $ma_phieu_ban_hang);
    $query->where('ct.so_luong > ct.xuat_kho');
    $result = $query->execute();
    $i = 0;
    while ($rs = $result->fetchObject()) {
      $so_luong = $rs->so_luong - $rs->xuat_kho;
      //      if ($so_luong) {
      $i++;
      $data[] = array(
        $i,
        $rs->ma_thanh_pham,
        $rs->ten_thanh_pham,
        $so_luong,
        $rs->tru_tien ? number_format($rs->tru_tien) : '',
        $rs->tru_tien ? number_format($so_luong * $rs->tru_tien) : '',
      );
      //      }
    }
    if (!$i) {
      return array();
    }
    return $data;
  }

  function get_thong_ke_san_pham($sp, $type, $start_time, $end_time) {
    switch ($type) {
      case 'nhap':
        $query = db_select('dt_san_pham_nhap', 'spn')->extend('PagerDefault');
        $query->fields('spn', array('id', 'ma_phieu_nhap', 'ma_to_san_xuat', 'thoi_gian'));
        $query->condition('spn.thoi_gian', $start_time, '>=');
        $query->condition('spn.thoi_gian', $end_time, '<=');
        $query->leftJoin('dt_to_san_xuat', 'tsx', 'tsx.ma_to_san_xuat = spn.ma_to_san_xuat');
        $query->addField('tsx', 'ten_to_san_xuat');
        $query->leftJoin('dt_san_pham_nhap_chi_tiet', 'spnct', 'spnct.ma_phieu_nhap = spn.ma_phieu_nhap');
        $query->condition('spnct.ma_thanh_pham', $sp->ma_thanh_pham, '=');
        $result = $query->orderBy('spn.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Bộ phận',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            'ma_phieu_nhap' => $rs->ma_phieu_nhap,
            'ma_to_san_xuat' => $rs->ten_to_san_xuat,
            'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
            'thao_tac' => l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/nhap/' . $rs->ma_phieu_nhap)
          );
        }
        break;
      case 'hoi':
        $query = db_select('dt_san_pham_hoi', 'sph')->extend('PagerDefault');
        $query->fields('sph', array('id', 'ma_phieu_hang_hoi', 'ma_khach_hang', 'thoi_gian', 'trang_thai'));
        $query->condition('sph.thoi_gian', $start_time, '>=');
        $query->condition('sph.thoi_gian', $end_time, '<=');
        $query->leftJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = sph.ma_khach_hang');
        $query->addField('kh', 'ten_khach_hang');
        $query->leftJoin('dt_san_pham_hoi_chi_tiet', 'sphct', 'sphct.ma_phieu_hang_hoi = sph.ma_phieu_hang_hoi');
        $query->condition('sphct.ma_thanh_pham', $sp->ma_thanh_pham, '=');
        $result = $query->orderBy('sph.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $header = array(
          'Mã phiếu',
          'Khách hàng',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            'ma_phieu_hang_hoi' => $rs->ma_phieu_hang_hoi,
            'ma_khach_hang' => $rs->ten_khach_hang,
            'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
            'thao_tac' => l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/hoi/' . $rs->ma_phieu_hang_hoi)
          );
        }
        break;
      case 'xuat':
        $query = db_select('dt_ban_hang', 'bh')->extend('PagerDefault')->extend('TableSort');
        $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
        $query->condition('bh.thoi_gian', $start_time, '>=');
        $query->condition('bh.thoi_gian', $end_time, '<=');
        $query->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
        $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
        $query->leftJoin('dt_ban_hang_chi_tiet_so_luong', 'xkct', 'xkct.ma_phieu_ban_hang = bh.ma_phieu_ban_hang');
        $query->condition('xkct.ma_thanh_pham', $sp->ma_thanh_pham, '=');
        $result = $query->orderBy('bh.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $option_tt = get_option_trang_thai_ban_hang();
        $option_bht = get_option_trang_thai_bao_hanh_truoc();
        $header = array(
          'Mã phiếu',
          'Tên khách hàng',
          'Tổng tiền',
          'Tổng tiền trừ',
          'Tiền bớt',
          'Thực thu',
          'Bảo hành trước',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            $rs->ma_phieu_ban_hang,
            $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
            number_format($rs->tong_tien),
            number_format($rs->tong_tien_tru),
            number_format($rs->tong_tien - $rs->tong_tien_tru - $rs->thuc_thu),
            number_format($rs->thuc_thu),
            $option_bht[$rs->trang_thai_bao_hanh_truoc],
            date('H:i:s d/m/Y', $rs->thoi_gian),
            l('Chi tiết', 'san_pham/ban_hang/in_phieu_ban_hang/ban_hang/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
          );
        }
        break;
      case 'kmai':
        $query = db_select('dt_ban_hang', 'bh')->extend('PagerDefault')->extend('TableSort');
        $query->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
        $query->condition('bh.thoi_gian', $start_time, '>=');
        $query->condition('bh.thoi_gian', $end_time, '<=');
        $query->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
        $query->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
        $query->leftJoin('dt_ban_hang_chi_tiet_khuyen_mai', 'kmct', 'kmct.ma_phieu_ban_hang = bh.ma_phieu_ban_hang');
        $query->condition('kmct.ma_thanh_pham', $sp->ma_thanh_pham, '=');
        $query->condition('kmct.tru_tien', 0, '=');
        $result = $query->orderBy('bh.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $option_tt = get_option_trang_thai_ban_hang();
        $option_bht = get_option_trang_thai_bao_hanh_truoc();
        $header = array(
          'Mã phiếu',
          'Tên khách hàng',
          'Tổng tiền',
          'Tổng tiền trừ',
          'Tiền bớt',
          'Thực thu',
          'Bảo hành trước',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            $rs->ma_phieu_ban_hang,
            $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
            number_format($rs->tong_tien),
            number_format($rs->tong_tien_tru),
            number_format($rs->tong_tien - $rs->tong_tien_tru - $rs->thuc_thu),
            number_format($rs->thuc_thu),
            $option_bht[$rs->trang_thai_bao_hanh_truoc],
            date('H:i:s d/m/Y', $rs->thoi_gian),
            l('Chi tiết', 'san_pham/ban_hang/in_phieu_ban_hang/ban_hang/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
          );
        }
        break;
      case 'bh':
        $query_x = db_select('dt_ban_hang', 'bh');
        $query_x->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
        $query_x->condition('bh.thoi_gian', $start_time, '>=');
        $query_x->condition('bh.thoi_gian', $end_time, '<=');
        $query_x->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
        $query_x->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
        $query_x->leftJoin('dt_ban_hang_chi_tiet_bao_hanh_truoc', 'bht', 'bht.ma_phieu_ban_hang = bh.ma_phieu_ban_hang');
        $query_x->condition('bht.ma_thanh_pham', $sp->ma_thanh_pham, '=');
        $query_n = db_select('dt_ban_hang', 'bh');
        $query_n->innerJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = bh.ma_khach_hang');
        $query_n->condition('bh.thoi_gian', $start_time, '>=');
        $query_n->condition('bh.thoi_gian', $end_time, '<=');
        $query_n->fields('bh', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian'));
        $query_n->fields('kh', array('ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
        $query_n->leftJoin('dt_ban_hang_chi_tiet_duoc_bao_hanh', 'p', 'p.ma_phieu_ban_hang = bh.ma_phieu_ban_hang');
        $query_n->leftJoin('dt_san_pham_hoi_chi_tiet', 'ct', 'p.id_chi_tiet_hoi = ct.id');
        $query_n->condition('ct.ma_thanh_pham', $sp->ma_thanh_pham, '=');
        $query_x->union($query_n, 'UNION ALL');
        $query = db_select($query_x, 'view')->extend('PagerDefault')->extend('TableSort');
        $query->fields('view', array('id', 'ma_phieu_ban_hang', 'tong_tien', 'thuc_thu', 'tong_tien_tru', 'trang_thai', 'trang_thai_bao_hanh_truoc', 'ghi_chu', 'thoi_gian', 'ma_khach_hang', 'ten_khach_hang', 'loai', 'tien_no'));
        $query->groupBy('view.id');
        $result = $query->orderBy('view.thoi_gian', 'DESC')->limit(20)->execute();
        $data = array();
        $option_tt = get_option_trang_thai_ban_hang();
        $option_bht = get_option_trang_thai_bao_hanh_truoc();
        $header = array(
          'Mã phiếu',
          'Tên khách hàng',
          'Tổng tiền',
          'Tổng tiền trừ',
          'Tiền bớt',
          'Thực thu',
          'Bảo hành trước',
          'Ngày tháng',
          'Thao tác'
        );
        while ($rs = $result->fetchObject()) {
          $data[] = array(
            $rs->ma_phieu_ban_hang,
            $rs->ten_khach_hang . ' [' . $rs->ma_khach_hang . ']',
            number_format($rs->tong_tien),
            number_format($rs->tong_tien_tru),
            number_format($rs->tong_tien - $rs->tong_tien_tru - $rs->thuc_thu),
            number_format($rs->thuc_thu),
            $option_bht[$rs->trang_thai_bao_hanh_truoc],
            date('H:i:s d/m/Y', $rs->thoi_gian),
            l('Chi tiết', 'san_pham/ban_hang/in_phieu_ban_hang/ban_hang/' . $rs->ma_phieu_ban_hang) . dt_view_ghi_chu('bh_' . $rs->id, 'Ghi chú phiếu bán hàng ' . $rs->ma_phieu_ban_hang, $rs->ghi_chu)
          );
        }
        break;
      case 'nhapnguyen':
        $query = db_select('dt_san_pham_nguyen', 'spng')->extend('PagerDefault')->extend('TableSort');
        $query->fields('spng', array('id', 'ma_phieu_hang_nguyen', 'ma_khach_hang', 'thoi_gian', 'trang_thai', 'tong_tien'));
        $query->condition('spng.thoi_gian', $start_time, '>=');
        $query->condition('spng.thoi_gian', $end_time, '<=');
        $query->leftJoin('dt_khach_hang', 'kh', 'kh.ma_khach_hang = spng.ma_khach_hang');
        $query->addField('kh', 'ten_khach_hang');
        $query->leftJoin('dt_san_pham_nguyen_chi_tiet', 'spngct', 'spngct.ma_phieu_hang_nguyen = spng.ma_phieu_hang_nguyen');
        $query->condition('spngct.ma_thanh_pham', $sp->ma_thanh_pham, '=');
        $result = $query->orderBy('spng.thoi_gian', 'DESC')->limit(20)->execute();
        $quyen = user_access('xem gia san pham');
        //$nhap_gia = (in_array($type, array('nguyen')) && user_access('nhap gia san pham'));
        $data = array();
        if ($quyen) {
          $header = array(
            'Mã phiếu',
            'Khách hàng',
            'Tổng tiền',
            'Trạng thái',
            'Ngày tháng',
            'Thao tác'
          );
        }
        else {
          $header = array(
            'Mã phiếu',
            'Khách hàng',
            'Ngày tháng',
            'Thao tác'
          );
        }
        while ($rs = $result->fetchObject()) {
          if ($quyen) {
            $data[] = array(
              'ma_phieu_hang_nguyen' => $rs->ma_phieu_hang_nguyen,
              'ma_khach_hang' => $rs->ten_khach_hang,
              'tong_tien' => number_format($rs->tong_tien),
              'trang_thai' => $rs->tong_tien ? 'Đã nhập giá tiền' : 'Chưa nhập giá tiền',
              'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
              'thao_tac' => l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/nguyen/' . $rs->ma_phieu_hang_nguyen)
            );
          }
          else {
            $data[] = array(
              'ma_phieu_hang_nguyen' => $rs->ma_phieu_hang_nguyen,
              'ma_khach_hang' => $rs->ten_khach_hang,
              'thoi_gian' => date('H:i:s d/m/Y', $rs->thoi_gian),
              'thao_tac' => l('Chi tiết', 'san_pham/phieu_xuat_nhap/in_phieu/nguyen/' . $rs->ma_phieu_hang_nguyen)
            );
          }
        }
        break;
    }
    return array('header' => $header, 'rows' => $data);
  }

  function update_tien_khuyen_mai_dau_thang($ma_khach_hang) {
    module_load_include('inc', 'khach_hang', 'model/khach_hang');
    $du_km = get_khach_hang_theo_ma($ma_khach_hang)->du_km;
    $month = date('n');
    $year = date('Y');
    $end_month = mktime(0, 0, 0, $month + 1, 1, $year) - 1;
    db_update('dt_khach_hang')
      ->fields(array(
                    //                 'uid' => $uid,
                    'du_km_dau_thang' => $du_km,
                    'thoi_gian_mua_cuoi' => $end_month,
               ))
      ->condition('ma_khach_hang', $ma_khach_hang)
      ->execute();
  }

  function update_du_khach_hang_km($du_khach_hang_km, $ma_khach_hang) {
    db_update('dt_khach_hang')
      ->fields(array(
                    //                 'uid' => $uid,
                    'du_km' => $du_khach_hang_km,
                    //                 'thoi_gian' => $thoi_gian,
               ))
      ->condition('ma_khach_hang', $ma_khach_hang)
      ->execute();
  }

  function xoa_san_pham($ma_san_pham) {
    db_update('dt_san_pham')
      ->fields(array(
                    'hien_thi' => 1,
               ))
      ->condition('ma_thanh_pham', $ma_san_pham)
      ->execute();
  }

  function xoa_phieu_thanh_pham($type, $ma_phieu, $phieu) {
    switch ($type) {
      case 'nhap':
        db_delete('dt_san_pham_nhap')
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_thanh_pham_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $san_pham)
        {
          $ma_sp = $san_pham->ma_thanh_pham;
          $so_luong = $san_pham->so_luong;
          update_so_luong_san_pham('-', $so_luong, $ma_sp);
        }
        db_delete('dt_san_pham_nhap_chi_tiet')
          ->condition('ma_phieu_nhap', $ma_phieu)
          ->execute();
        break;
      case 'hoi':
        db_delete('dt_san_pham_hoi')
          ->condition('ma_phieu_hang_hoi', $ma_phieu)
          ->execute();
        db_delete('dt_san_pham_hoi_chi_tiet')
          ->condition('ma_phieu_hang_hoi', $ma_phieu)
          ->execute();
        break;
      case 'nguyen':
        module_load_include('inc', 'khach_hang', 'model/khach_hang');
        update_tien_no_khach_hang('+', $phieu->tong_tien, $phieu->ma_ref);
        db_delete('dt_san_pham_nguyen')
          ->condition('ma_phieu_hang_nguyen', $ma_phieu)
          ->execute();
        $data = (array) get_phieu_thanh_pham_chi_tiet_theo_ma($type, $ma_phieu, false);
        foreach ($data as $san_pham)
        {
          $ma_sp = $san_pham->ma_thanh_pham;
          $so_luong = $san_pham->so_luong;
          update_so_luong_san_pham('-', $so_luong, $ma_sp);
        }
        db_delete('dt_san_pham_nguyen_chi_tiet')
          ->condition('ma_phieu_hang_nguyen', $ma_phieu)
          ->execute();
        break;
      case 'hong':
        db_delete('dt_xuat_san_pham_hong')
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        db_delete('dt_xuat_san_pham_hong_chi_tiet')
          ->condition('ma_phieu_xuat', $ma_phieu)
          ->execute();
        break;
      case 'banhang':
        $data = (array) get_ban_hang_so_luong_lst($ma_phieu);
        foreach ($data as $san_pham)
        {
          $ma_sp = $san_pham->ma_thanh_pham;
          $so_luong = $san_pham->xuat_kho;
          update_so_luong_san_pham('+', $so_luong, $ma_sp);
        }
        db_update('dt_khach_hang')
          ->expression('tien_no', 'tien_no ' . '-' . ' :tien_no', array(':tien_no' => $phieu->thuc_thu))
          ->condition('ma_khach_hang', $phieu->ma_khach_hang)
          ->execute();
        $data_hoi = (array) get_ban_hang_duoc_bao_hang_lst($ma_phieu);
        foreach ($data_hoi as $chi_tiet_hoi)
        {
          db_update('dt_san_pham_hoi')
            ->fields(array(
                          'trang_thai_bao_hanh' => 0,
                     ))
            ->condition('ma_phieu_hang_hoi', $chi_tiet_hoi->ma_phieu_hang_hoi)
            ->execute();
        }
        db_delete('dt_ban_hang')
          ->condition('ma_phieu_ban_hang', $ma_phieu)
          ->execute();
        db_delete('dt_ban_hang_chi_tiet_bao_hanh_truoc')
          ->condition('ma_phieu_ban_hang', $ma_phieu)
          ->execute();
        db_delete('dt_ban_hang_chi_tiet_duoc_bao_hanh')
          ->condition('ma_phieu_ban_hang', $ma_phieu)
          ->execute();
        db_delete('dt_ban_hang_chi_tiet_hang_mua')
          ->condition('ma_phieu_ban_hang', $ma_phieu)
          ->execute();
        db_delete('dt_ban_hang_chi_tiet_hang_no')
          ->condition('ma_phieu_ban_hang', $ma_phieu)
          ->execute();
        db_delete('dt_ban_hang_chi_tiet_khuyen_mai')
          ->condition('ma_phieu_ban_hang', $ma_phieu)
          ->execute();
        db_delete('dt_ban_hang_chi_tiet_so_luong')
          ->condition('ma_phieu_ban_hang', $ma_phieu)
          ->execute();
        db_delete('dt_ban_hang_tra_no_bht')
          ->condition('ma_phieu_ban_hang', $ma_phieu)
          ->execute();
        break;
    }
  }

  function get_phieu_ban_hang_chua_thuc_thu() {
    $query = db_select('dt_ban_hang', 'p');
    $query->fields('p', array('id', 'ma_phieu_ban_hang', 'trang_thai', 'thoi_gian'));
    $query->condition('p.trang_thai', 0);
    $result = $query->execute();
    $data = array();
    while ($rs = $result->fetchObject()) {
      $data[] = $rs;
    }
    return $data;
  }

  function get_san_pham_theo_ten($ten_san_pham) {
    $query = db_select('dt_san_pham', 'sp');
    $query->fields('sp', array('id', 'ma_thanh_pham', 'ten_thanh_pham', 'hien_thi'));
    $query->condition('sp.ten_thanh_pham', $ten_san_pham, '=');
    $query->condition('sp.hien_thi', 0);
    $result = $query->execute();
    $data_khten = '';
    while ($rs = $result->fetchObject()) {
      $data_khten = $rs->ma_thanh_pham;
    }
    return $data_khten;
  }